In Excel, how to MATCH to an array when I know the row number

0
votes

I want to search a RANGE for the value "Yes" and return TRUE if I find it. I know the column numbers and I know the row number, but because the row number was determined dynamically, I cannot hard-code it.

For example, if I COULD hard-code it, my formula would be...

=IF( (MATCH("Yes",$E$60:$K$60,0)) > 0, TRUE,FALSE  )

The number "60" in this formula is dynamic and must be derived from another cell that stores that value of "60".

How do I replace the number "60" with a REFERENCE to another cell (that contains the number)? For example, the cell that contains the number 60 (which was derived by another formula) is cell B44.

So, what I want to do is say ...

=IF( (MATCH("Yes",$E${B44}:$K${B44},0)) > 0, TRUE,FALSE  )

Thanks.

2
excelexcel-formula

2 Answers

2
votes

Use Indirect Function(string as formula) for the part of the formula you need to be dynamic.

=IF( (MATCH("Yes",INDIRECT("$E$" & $B$44 & ":$K$" & $B$44),0)) > 0, TRUE,FALSE )
2
votes

Use of INDIRECT or OFFSET produces a volitile function (one that is evaluated every time the sheet is calculated, regardless of if any of its inputs has changed). This can slow down your sheet calculation considerably.

Your formula as it stands does not behave as you expect: if there is no Yes in the range, it returns an error, not FALSE

To get a non-volitile dynamic row reference try

=IFERROR( (MATCH("Yes",INDEX($E:$K,$B$44,0),0))>0,FALSE )