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:

CRM ID	Full 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

 

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

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.