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

#### squarelyjc

##### New Member
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.

Here's some sample 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>

 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
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
2123456Rob's PlaceRobert SmithRob.Smith@email.com
36543210Tom's PlaceTom WashingtonTom.Washington@email.com
4
5
6
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

Replies
1
Views
252
Replies
5
Views
540
Replies
6
Views
349
Replies
1
Views
242
Replies
3
Views
765

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.

### Which adblocker are you using?

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

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