dimanche 31 mai 2020

Error while indexing an array in google sheet

Formula1 ArrayFormula(regexextract(ArrayFormula(address(1,sequence(150,1,1,1),4)),"\D+"))

Formula2 if(and(5>=column(indirect(B2)),5<=columns(indirect(B2))+column(indirect(B2))-1),5,-1)

B2 contains range E1:G4

Formula1 & 2 are working perfectly fine but when we combine both as mentioned below there is an error: "Function INDEX parameter 2 value is -1. Valid values are between 0 and 150 inclusive."

index(ArrayFormula(regexextract(ArrayFormula(address(1,sequence(150,1,1,1),4)),"\D+")),if(and(5>=column(indirect(B2)),5<=columns(indirect(B2))+column(indirect(B2))-1),5,-1))

I have deliberately used -1 which is not a valued value for index parameter 2 so that it gives error when "If" condition is unfulfilled and I can omit whole result with iferror function, but when condition is fulfilled then it shouldn't give any error. Any work around will not help me because I need to use this formula component in another complex formula.

Google Sheet with function

Thanks in advance.

Aucun commentaire:

Enregistrer un commentaire