In an Excel sheet can I MATCH a cell using more than one lookup value ?

Hi All,

Is it possible to perform a MATCH using more than one lookup value/range ?

For example:

=MATCH(A1,F:F,0) will return the row number if a match is found in column F of the value in A1

What if I want to find the row number when more than one match/column condition exists ?

For example:

=MATCH(A1,F:F,0) and MATCH(B1,G:G,0) must both be TRUE before the row number is returned

Thanks in anticipation
Toco
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
0
Commented:
This should work:
=MATCH(A1,IF(G:G=B1,F:F),0)

Also An array formula should do it:
=MATCH(A1&"|"&B1,F:F&"|"&G:G,0)
enter with [ctrl]+[shift]+[enter]

I would specify the range of rows though as with the big excel sheet it may get slow:
=MATCH(A1&"|"&B1,F1:F9999&"|"&G1:G9999,0)
enter with [ctrl]+[shift]+[enter]

*note: I use a pipe in my concatenation as it is not good practice to exclude a delimiter
10:1 is the same as 1:01 if you do not use a delimiter.
0

Experts Exchange Solution brought to you by