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?


Posted by Aladin Akyurek on May 29, 2001 9:55 AM


I'll assume a sheet named 'query" and 2 others named ws1 and ws2.

On ws1 in A1:B3 we have


On ws2 in A1:B3 we have


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:




Posted by Aladin Akyurek on May 29, 2001 10:28 AM


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


Just put "aladin" in A4 on sheet query and try this out.