dimanche 5 décembre 2021

Cell-Address with an IF Statement

I've been using =Cell("address",... to error check formulas I'm making to create a database and it's been working pretty well until I tried to scale up my index match formulas with some if statements.

I've narrowed the issue down to where I believe it's working as intended as long as the first statement in the if statement is true, yet if the calculation goes past that (even if the original formula is fine and returns a value) it will come back with #VALUE!.

EDIT: To be clear, the formula used above returns values without issue when not using the CELL-ADDRESS function involved. The formula also returns values when split up outside the IF-Statement.

I2=Valid

=CELL("address",IF($I$2="Valid",INDEX($I$2:$J$6,MATCH($U$1,$H$2:$H$6,0),MATCH($V$1,$I$1:$J$1,0)),IF($I$2="Invalid",INDEX($I$2:$J$6,MATCH($W$1,$H$2:$H$6,0),MATCH($V$1,$I$1:$J$1,0)),"Error")))

$I$2 is returned in the cell

I2=Invalid

=IF($I$2="Valid",INDEX($I$2:$J$6,MATCH($U$1,$H$2:$H$6,0),MATCH($V$1,$I$1:$J$1,0)),IF($I$2="Invalid",INDEX($I$2:$J$6,MATCH($W$1,$H$2:$H$6,0),MATCH($V$1,$I$1:$J$1,0)),"Error"))

#VALUE! is returned

Can anyone offer a fix for this or explain why it's happening and how to avoid it?

Any help would be appreciated.

Thanks!

Aucun commentaire:

Enregistrer un commentaire