MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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