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