Find the ROW number in excel with multiple matching criteria

Question

I need to be able to find the row number of the row where matching criteria from A1 is equal or greater than values in column C and lesser or equal than values in column D

I can use INDEX and MATCH combo but not sure if this is something I should use for multiple criteria matching.

Any help or suggestions are highly appreciated.

Answer 1

I would not use MATCH to get the row number since you have multiple criteria. I would still use INDEX however to get the value of the row in E once the proper row number was discovered.

So instead of MATCH I would use an array formula using an IF statement that contained multiple criteria. Now note that array formulas need to be entered using ctrl + shift + enter. The IF statement would look like this:

=IF((A1>=C:C)*(A1<=D:D),ROW(A:A),"")

Note: I did not use the AND formula here because that cannot take in arrays. But since booleans are just 1's or 0's in Excel, multiplying the criteria works just fine.

This now gives us an array containing only blanks and valid row numbers. Such that if rows 5 and 7 were both valid the array would look like:

{"","","","",5,"",7,"",...}

Now if we encapsulate that IF statement with a SMALL we can get whatever valid row we want. In this case since we just want the first valid row we can use:

=SMALL(IF((A1>=C:C)*(A1<=D:D),ROW(A:A),""),1)

Which if the first valid row is 5 then that will return 5. Incrementing the K value of the SMALL formula will allow you to grab the 2nd, 3rd, etc valid row.

Now of course since we have the row number a simple INDEX will get us the value in column E:

=INDEX(E:E,SMALL(IF((A1>=C:C)*(A1<=D:D),ROW(A:A),""),1))