Matching Data
Posted by Jeff on May 29, 2001 9:31 AM
I think this may be a tough one...
I have two worksheets with different sets of data:
WS 1: Customer Names and Customer Number.
WS 2: Customer email and Customer Number.
How do I match up the customer numbers on each sheet to pair up the customer names with their email?
JB
Posted by Aladin Akyurek on May 29, 2001 9:55 AM
Jeff
I'll assume a sheet named 'query" and 2 others named ws1 and ws2.
On ws1 in A1:B3 we have
{"Cnames","Cnums";"aladin",34;"jeff",35}
On ws2 in A1:B3 we have
{"C-email","Cnum";"akyurek@bart.nl",34;"jbordes@yahoo.com",35}
On sheet query:
In A2 enter: 34
In B2 enter: =IF(ISNUMBER(MATCH(A2,ws1!B2:B3,0)),INDEX(ws1!A2:A3,MATCH(A2,ws1!B2:B3,0)),"")
In C2 enter: =IF(ISNUMBER(MATCH(A2,ws2!B2:B3,0)),INDEX(ws2!A2:A3,MATCH(A2,ws2!B2:B3,0)),"")
For the customer 34, we get:
{34,"aladin","akyurek@bart.nl"}
Aladin
===============
Posted by Aladin Akyurek on May 29, 2001 10:28 AM
Jeff
It just occurred to me that you want use a name as lookup value and retrieve the email address associated with that name. In that case, use
=IF(ISNUMBER(MATCH(A4,ws1!A2:A3,0)),INDEX(ws2!A2:A3,MATCH(VLOOKUP(A4,ws1!A2:B3,2,0),ws2!B2:B3,0)),"")
Just put "aladin" in A4 on sheet query and try this out.
Aladin
