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

SwimmingNathan

New Member
Joined
Aug 21, 2013
Messages
34
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

marious

Board Regular
Joined
Mar 3, 2013
Messages
221
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

*ABCD
1CRM IDFull Name*Lookup
2d2f6vr61-5v5r11vdv1-d6vd6g6eSonny Jim*d2f6vr61-5v5r11vdv1-d6vd6g6e
3bRaul Perez**
4eRod Steward**
5aHelen McArthur**
6FHelen 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
CellFormula
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
Joined
Aug 21, 2013
Messages
34
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.
 

Forum statistics

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

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