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

[SwimmingNathan]

Hey,

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

First sheet:

[TABLE="width: 750"]
<tbody>[TR]
[TD]CRM ID[/TD]
[TD]Full Name[/TD]
[/TR]
[TR]
[TD]d2f6vr61-5v5r11vdv1-d6vd6g6e
[TABLE="width: 133"]
<tbody></tbody>[/TABLE]
[/TD]
[TD]Sonny Jim[/TD]
[/TR]
</tbody>[/TABLE]

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:

[TABLE="width: 750"]
<tbody>[TR]
[TD]Sonny Jim[/TD]
[TD]=INDEX('Customer contacts CEGX 08.08.13'!E:E, (MATCH(Opps!C4,'Customer contacts CEGX 08.08.13'!E:E,0)))[/TD]
[TD](I want to return the ID in for the name in the first column here.[/TD]
[/TR]
[TR]
[TD]Rod Stewart[/TD]
[TD]=INDEX('Customer contacts CEGX 08.08.13'!E:E, (MATCH(Opps!C4,'Customer contacts CEGX 08.08.13'!E:E,0)))[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Helen McArthur[/TD]
[TD]=INDEX('Customer contacts CEGX 08.08.13'!E:E, (MATCH(Opps!C4,'Customer contacts CEGX 08.08.13'!E:E,0)))[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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

 

[Chris Mack]

=INDEX(Other sheet!$A$2:$B$x,MATCH(A2,$B$2:$B$x,0),1)

 

[marious]

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
*
b	Raul Perez	*	*
e	Rod Steward	*	*
a	Helen McArthur	*	*
F	Helen McArthur	*	*
*	*	*	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>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="bgcolor: #ccccff, align: left"]CRM ID[/TD]
[TD="bgcolor: #ccccff, align: left"]Full Name[/TD]
[TD="bgcolor: #ccccff"]*[/TD]
[TD="bgcolor: #ccccff"]Lookup[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: left"]d2f6vr61-5v5r11vdv1-d6vd6g6e[/TD]
[TD="align: left"]Sonny Jim[/TD]

[TD="bgcolor: #ccffcc"]d2f6vr61-5v5r11vdv1-d6vd6g6e[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

</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]

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.