Can VLookUp work in this instance? If so, how as I can't get it working?

ExcelNoob89

New Member
Joined
Oct 2, 2013
Messages
25
Hi All,
I have the following dataset on Sheet 1 (i'll provide a rough sample below):

ISBNCTypeCIDFullName
1234567890A01John Smith
1275910285A07Joe Bloggs
7885419987A01Bob Thornton

<tbody>
</tbody>

I then have the following Information on Sheet 2

CIDFullName
7895123675John Smith
4210326985Joe Bloggs
75126548951Bob Thornton

<tbody>
</tbody>

What I need to do is hopefully with your help a bit of formula or code that can do the following:

The code will look at the FullName in Sheet 1 (55,000 Records) and in reference to sheet 2 (22,000 Records), it will search for the relevant FullName (22,000 unique records when it has the combination of CID and FullName) and populate the CID in Sheet 1 with the CID from Sheet 2.

I have tried to use the VLookUp function however it hasn't worked at all!

Any help is greatly appreciated!
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

ExcelNoob89

New Member
Joined
Oct 2, 2013
Messages
25
I've tried doing =VLookUp(D2,Sheet2!B1:B4,2,false) but I get #REF! as an error or I get #N/A! as an error.
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Vlookup can't look to the left (it's Zoolander Challenged)
It can only find a match for a value in one column, and return a value that is x columns 'to the right' of where the match was found.

You need Index/Match instead..

Try

=INDEX(Sheet2!A1:A4,MATCH(D2,Sheet2!B2:B4,0))
 

ExcelNoob89

New Member
Joined
Oct 2, 2013
Messages
25
I managed to get it working with the following formula.

=LOOKUP(H3,'Sheet2'!$B$2:$B$21002,'Sheet2'!$A$2:$A$21002)

Thank you for your reply anyway!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,414
Messages
5,596,001
Members
414,037
Latest member
Roamingsmile

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