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

[ExcelNoob89]

Hi All,
I have the following dataset on Sheet 1 (i'll provide a rough sample below):

ISBN	CType	CID	FullName
1234567890	A01		John Smith
1275910285	A07		Joe Bloggs
7885419987	A01		Bob Thornton

<tbody>
</tbody>

I then have the following Information on Sheet 2

CID	FullName
7895123675	John Smith
4210326985	Joe Bloggs
75126548951	Bob 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!

 

[ExcelNoob89]

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]

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]

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!