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

squarelyjc

New Member
Joined
Jan 27, 2018
Messages
1
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:

Customer ID
Business Name
Individual Name
Email Address
0123456
Rob's Place
Robert Smith
Rob.Smith@email.com
6543210
Tom's Place
Tom Washington
Tom.Washington@email.com

<tbody>
</tbody>

Spreadsheet 2- Transactions:
Customer ID
Business Name
Individual Name
Email Address
Transaction Date
Items Purchased
Robert Smith
Rob.Smith@email.com
1/1/2018
Corn
Robert Smith
Rob.Smith@email.com
1/15/2018
Carrots
Tom Washington
Tom.Washington@email.com
1/16/2018
Peas
Tom Washington
Tom.Washington@email.com
1/17/2018
Apples

<tbody>
</tbody>

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?
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,760
Office Version
  1. 365
Platform
  1. Windows
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.

Code:
[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
ABCDEF
1Customer IDBusiness NameIndividual NameEmail Address
2123456Rob's PlaceRobert SmithRob.Smith@email.com
36543210Tom's PlaceTom WashingtonTom.Washington@email.com
4
5
6
7Customer IDBusiness NameIndividual NameEmail AddressTransaction DateItems Purchased
8123456Rob's PlaceRobert SmithRob.Smith@email.com1/1/2018Corn
9123456Rob's PlaceRobert SmithRob.Smith@email.com1/15/2018Carrots
106543210Tom's PlaceTom WashingtonTom.Washington@email.com1/16/2018Peas
116543210Tom's PlaceTom WashingtonTom.Washington@email.com1/17/2018Apples
Sheet
 

Watch MrExcel Video

Forum statistics

Threads
1,130,373
Messages
5,641,772
Members
417,235
Latest member
MistaMista

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top