Lookup & Return Data from Different Column

[TBeverson]

I have a book with multiple tabs in it. The first is a master customer file, including customer numbers. The next two tabs include multiple references to the same customers because they list multiple shipping addresses and contacts for each customer on a separate line. I want to be able to have the additional tabs refer to the master list and find the customer name, then return the corresponding customer # in the previous column for that customer. This is what I came up with: =IF(B:B=Sheet2!B:B,Sheet1!A:A,0). But the results simply returns numbers that increase by 1. It should repeat the same customer # for the number of times that customer name is listed based on how many addresses or contacts there are. What am I not doing right?

[shg]

Post an example workbook with sensitive info removed?

[TBeverson]

How do I add an attachment to my post? Is that possible in this forum? Or do you mean paste it here in my post.

[Domenic]

When you post/reply to thread, scroll down until you see 'Manage Attachments' under 'Additional Options'...

[TBeverson]

Ok, I see it now, thanks! I attached my sample. It is truly just a sample and there is no sensitive data. Hopefully someone can tell me what I'm doing wrong. Thanks!

[Domenic]

Unclear... Can you elaborate?

[TBeverson]

The first tab (entitled Master) within this workbook is a sample master customer record, with Column A being the customer # and Column B being the customer name. The second tab (entitled Multiple) as you can see has each sample customer name listed twice in Column B. What I want to be able to do is have Column A in the Multiple tab refer to the Master tab and find the customer name, then return the corresponding customer # in Column A from the Master tab on that same row to Column A in the Multiple tab. So Column A in the Multiple tab should list each customer # twice. How I have the function written currently, it is supposed to return a 0 only if it can't find the customer name in the Master tab. The way I have it written already is returning a customer # then just adding 1 to each subsequent one. Help!?!

[Domenic]

Try...

A1, copied down:

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,INDEX(master!$A$1:$A$18,MATCH(multiple!B1,master!$B$1:$B$18,0))))

[TBeverson]

THANK YOU THANK YOU THANK YOU!!! You have no idea how much time you saved me. I am going to tell all of my friends about this site. You rock!!!