Need to match data on multple tables

I am running Excel 2007. I've searched for answers to this but nothing seems to work. I'll try to give as concise an explanation as possible:

I am creating a report to match a customer number to a customer when it appears on an order. However, the order only lists the invoice number and not the customer number. I need to cross reference the invoice to another master list that has the name, CID, and invoice, and show the customer number as a result on the invoice report.

I attached a sample to get the jist of what I'm trying to do (and yes the invoice report may not be in order so I need to be sure the name and invoice match the first list)

I have these reports on 2 tabs in the same workbook.

Side Note: I manually matched (hopefully) these two lists in one sheet (2 tables) and wonder if there is a way to have excel look at say B2 and M2 and if the name matches to place an "X" on the cell beside it?

Thank you! This is really time sensitive and don't want to go through hundreds of customers manually to input the info.

Thank you again!

Ideally I want to get the names and invoices in the second table/report to look at the first table/report and in the green column display the customer number.


I started to do this manually and matched the names and invoices using conditional formatting (hoping it worked)and want to validate that I matched them correctly. [Mainly why I am asking the first question since I don't want to match manually and have excel just cross reference the data] If it's confusing, ignore the "X" question because what I really need is the CID

Thanks Tony. That works on the test file, now to try the main project and hope I don't crash the system lol. I'll advise in a bit!

I tried both formulas on the test file and it works, but in the real report I get N/A results. I even tried it on like 20 entries instead of the 1000+ but no different. and I know there should be at least 2 positive results if the formula would work as intended. not sure what can be causing the problem

I think there is something in the data that the formula is not liking. I'm thinking its the name. How do I change the Array Formula to only pick up the invoice number and return the CID?

Attached is a more accurate example of what I am using. Here the Array formula worked, but the VLOOKUP did not. not sure why. I cleaned the cell data on the original report but neither formula works yet. Trying to get an understanding of how the formula works and see if building it from scratch may help.

thank you all for your help so far :D

Thanks Tony that worked! So far so good. Now for the bonus question.

Can I use the same formula to do the same thing but the array list is on a separate sheet. In other words, instead of looking at the table of data on the left, it would be the same table of data on the "CID" tab. If this works, you would have saved us countless hours of manpower!

YES!!! It worked. I had to clean the data as the download added characters that were throwing it off, but I got it all to work. (It was 3 reports I was comparing in one final report) but it all worked out.

THANK YOU!!!