MrExcel Publishing
Your One Stop for Excel Tips & Solutions

vlookup (or index or match) Multi criteria matching


Posted by hassan Omar on November 02, 2001 6:44 AM

I'm trying to match data from 2 tables by two sets of matching criteria.

table 1
a1 ACCOUNT,b1 #100,c1 #101,++ah1#129
a2 10/1/01, b2 ++ah2 value returned from matching table by date and account
a3 10/2/01, b3 ++ah3 value returned from matching table by date and account
a4 10/3/01, b4 ++ah4 value returned from matching table by date and account
a5 10/4/01, b5 ++ah5 value returned from matching table by date and account
++
a32 10/31/01, b32 ++ah32 value returned from matching table by date and account

Matching table

ak1 ACCOUNT, al1 DATE, am1 AMOUNT
ak2 #100, al2 10/1/01, am2 $15.40
ak3 #100, al3 10/5/01, am3 $5.40
ak4 #101, al4 10/8/01, am4 $4.40
ak5 #129, al5 10/31/01, am5 $6.28
ak6 #110, al6 10/8/01, am6 $5.40
ak7 #125, al7 10/1/01, am7 $6.86

so that values from the matching table are returned to the corresponding cell by account/date in table 1, as follows:

a1 ACCOUNT,b1 #100,c1 #101,++ah1#129
a2 10/1/01, b2 $15.40, z2 $6.86
++
a6 10/5/01, b6 $5.40
++
a32 10/31/01, ah32 $6.86

in other words, goto b1, find match at column ak. From adjacent cell in column aj, match the date to the date in column a. return the value from the adjacent cell in column al to the date/account value cell in column b.


Posted by hassan omar on November 02, 2001 10:54 AM

all this really is is a double lookup. Help, please!!

I'm lost on this one

Posted by Aladin Akyurek on November 02, 2001 12:30 PM

Re: all this really is is a double lookup. Help, please!!

The description you provided is (for me at least) hard to follow. Perhaps you should try to tell what ranges are involved, the data type of these ranges along with lookup values and the location (columns) where the associated values are.

Aladin