VLOOKUP, INDEX, MATCH... What do I use for this scenario?

[squarelyjc]

I have two separate workbooks that include one spreadsheet of data each. One is a lists of all of our B2B customer information - Customer ID, Business Name, Individual Name, and Email Address. We will call this spreadsheet Customer Data.

The second spreadsheet, located in a different workbook, is a list of all transactions each customer has completed with us. We will call it Transactions.

The catch is that the transactions spreadsheet, which lists tens of thousands of transactions, does not include Customer ID or Business Name, which is the information we need to load the data into our CRM system.

I would like the first two columns of the Transactions spreadsheet to be Customer ID and Business Name, respectively. I want to auto-fill these using a formula. The email address is most unique data point that exists on both sheets (each customer can only use one email address, ever, because of the nature of our service). I'd like to search for the email address listed on the Transactions spreadsheet in the Customer Data spreadsheet, and then return both the Customer ID and Business Name from the Customer Data spreadsheet to every corresponding transaction with that email address in the first two columns of the transactions spreadsheet.

Here's some sample data:

Spreadsheet 1 - Customer Data:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Customer ID
[/TD]
[TD]Business Name
[/TD]
[TD]Individual Name
[/TD]
[TD]Email Address
[/TD]
[/TR]
[TR]
[TD]0123456
[/TD]
[TD]Rob's Place
[/TD]
[TD]Robert Smith
[/TD]
[TD]Rob.Smith@email.com
[/TD]
[/TR]
[TR]
[TD]6543210
[/TD]
[TD]Tom's Place
[/TD]
[TD]Tom Washington
[/TD]
[TD]Tom.Washington@email.com
[/TD]
[/TR]
</tbody>[/TABLE]

Spreadsheet 2- Transactions:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Customer ID
[/TD]
[TD]Business Name
[/TD]
[TD]Individual Name
[/TD]
[TD]Email Address
[/TD]
[TD]Transaction Date
[/TD]
[TD]Items Purchased
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Robert Smith
[/TD]
[TD]Rob.Smith@email.com
[/TD]
[TD]1/1/2018
[/TD]
[TD]Corn
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Robert Smith
[/TD]
[TD]Rob.Smith@email.com
[/TD]
[TD]1/15/2018
[/TD]
[TD]Carrots
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Tom Washington
[/TD]
[TD]Tom.Washington@email.com
[/TD]
[TD]1/16/2018
[/TD]
[TD]Peas
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Tom Washington
[/TD]
[TD]Tom.Washington@email.com
[/TD]
[TD]1/17/2018
[/TD]
[TD]Apples
[/TD]
[/TR]
</tbody>[/TABLE]

The desired result is that in the first two rows of the Transactions spreadsheet, Rob's Customer ID and Business Name would be listed. Then for 3 & 4 rows, Tom's would be listed. This will go on for thousands of customers and exponentially more transactions.

Can someone help?

 

[AhoyNC]

Try using INDEX / MATCH:

You could also us VLOOKUP for the Business Name column. Since you have a large database not sure if INDEX or VLOOKUP would be faster in column B.

[TABLE="width: 239"]
<colgroup><col width="239"></colgroup>[TR]
   [TD="width: 239"]=VLOOKUP($A8,$A$2:$B$3,2,0)[/TD]
 [/TR]
[/TABLE]

Excel Workbook
	A	B	C	D	E	F
1	Customer ID	Business Name	Individual Name	Email Address
2	123456	Rob's Place	Robert Smith	Rob.Smith@email.com
3	6543210	Tom's Place	Tom Washington	Tom.Washington@email.com
4
5
6
7	Customer ID	Business Name	Individual Name	Email Address	Transaction Date	Items Purchased
8	123456	Rob's Place	Robert Smith	Rob.Smith@email.com	1/1/2018	Corn
9	123456	Rob's Place	Robert Smith	Rob.Smith@email.com	1/15/2018	Carrots
10	6543210	Tom's Place	Tom Washington	Tom.Washington@email.com	1/16/2018	Peas
11	6543210	Tom's Place	Tom Washington	Tom.Washington@email.com	1/17/2018	Apples
Sheet