V-lookup with two columns?

Posted by Jed on February 11, 2002 8:41 AM

There are two sets of data that have two fields in common. How can I have excel return a match only when both fields of data match? Is there some way to modify v-lookup, or is there a completly different way to go about this?

Posted by Aladin Akyurek on February 11, 2002 8:51 AM

Jed --

Care to elaborate a bit more by providing 5 rows of data from each of the two sets of data, along with what your lookup value is and the expected result?

Aladin

==========

Posted by Jed on February 11, 2002 10:59 AM

Lets say I have in the first data set Account#, Price and date. In the second data set I have Acct#, Price plus several others.

Acct# Price Flag Acct# Price Flag
555 55 Match 522 55 Match
549 30 Match 555 55 Match
522 11 no match 549 30 Match
522 55 Match 787 25 no match

I woul like to match up these two sets of data
but only if Acct# and price match.

any ideas?

Posted by Aladin Akyurek on February 11, 2002 11:41 AM

Jed --

Assuming that A1:B5 houses the first sample set inncluding labels and D1:E5 the second set again with labels:

In C2 enter: =SUMPRODUCT((ISNUMBER(MATCH(A2&"@"&B2,$D$2:$D$5&"@"&$E$2:$E$5,0))+0))

Drag this down till C5.

Adapt this formula wrt cell and range refs and enter it also in F2 and drag down.

Select C2:C5 and custom format the range on the Number tab (via Format Cells) as:

[=1]"Match";[=0]"No Match";General

Do the same for F2:F5.

=SUM(C2:C5) will give you a count of matches.

Aladin

=======