# Formula to search for an ID in a column, based on a name in the same row.

#### SwimmingNathan

##### New Member
Hey,

So I have a spreadsheet with two sheets, on the first is a list of contact details, including CRM ID numbers.

First sheet:

CRM IDFull Name
d2f6vr61-5v5r11vdv1-d6vd6g6e

<tbody></tbody>
Sonny Jim

<tbody>
</tbody>

On the second sheet I have details of opportunities, the only identifiable variable on this sheet is the customer name.

I want to search the first sheet based on the name and return the ID.

Second sheet:

 Sonny Jim =INDEX('Customer contacts CEGX 08.08.13'!E:E, (MATCH(Opps!C4,'Customer contacts CEGX 08.08.13'!E:E,0))) (I want to return the ID in for the name in the first column here. Rod Stewart =INDEX('Customer contacts CEGX 08.08.13'!E:E, (MATCH(Opps!C4,'Customer contacts CEGX 08.08.13'!E:E,0))) Helen McArthur =INDEX('Customer contacts CEGX 08.08.13'!E:E, (MATCH(Opps!C4,'Customer contacts CEGX 08.08.13'!E:E,0)))

<tbody>
</tbody>

The formula in the second column contains a formula I'm using to check whether the names are actually in the first sheet.

I can re-configure the layout of the spreadsheet if needs be, I'm sure there's a fairly simple solution, my brain just is not playing ball.

Any help is greatly appreciated.

Cheers,

SwimmingNathan

### Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

#### Chris Mack

##### Well-known Member
Code:
``=INDEX(Other sheet!\$A\$2:\$B\$x,MATCH(A2,\$B\$2:\$B\$x,0),1)``

#### marious

##### Board Regular
Hey,
SwimmingNathan
Can you adjust the ranges so the last value is locked both sides and the first value is unlocked by row “ no dollar sign” and then drag the formula down. This way you avoid having duplicate names matched to the wrong row when you use match. </SPAN>
For example:</SPAN>

<!-- ######### Start Created Html Code To Copy ########## -->
Sheet1

 * A B C D 1 CRM ID Full Name * Lookup 2 d2f6vr61-5v5r11vdv1-d6vd6g6e Sonny Jim * d2f6vr61-5v5r11vdv1-d6vd6g6e 3 b Raul Perez * * 4 e Rod Steward * * 5 a Helen McArthur * * 6 F Helen McArthur * * 7 * * * Drag the formula down

<COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 245px"><COL style="WIDTH: 203px"><COL style="WIDTH: 64px"><COL style="WIDTH: 140px"></COLGROUP><TBODY>
</TBODY>

Spreadsheet Formulas
 Cell Formula D2 =INDEX(\$A2:\$A\$6,MATCH(B2,\$B2:\$B\$6,0))

<TBODY>
</TBODY>

<TBODY>
</TBODY>

Excel tables to the web >> Excel Jeanie HTML 4

<!-- ######### End Created Html Code To Copy ########## -->

#### SwimmingNathan

##### New Member
Can you adjust the ranges so the last value is locked both sides and the first value is unlocked by row “ no dollar sign” and then drag the formula down. This way you avoid having duplicate names matched to the wrong row when you use match.
For example:

Thanks to yourself and Chris Mack, I'm not sure what I was doing wrong initially but when I copied and pasted the two large columns into the same sheet those formulas worked, and now work with the data on a different sheet.

Thanks for the help.

Replies
7
Views
198
Replies
0
Views
375
Replies
3
Views
374
Replies
0
Views
77
Replies
0
Views
289

Threads
1,136,863
Messages
5,678,202
Members
419,750
Latest member
crp

### 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

### 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