CODEWITHSUNDEEP
exceldatatablevlookup
user3177630
user3177630

Reputation: 11

Excel - Lookup data in another sheet

I have the following dataset on Sheet 1:

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

I also have the following information on Sheet 2

CID         FullName
7895123675  John Smith
4210326985  Joe Bloggs
75126548951 Bob Thornton

What I want is a 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!

Upvotes: 0

Views: 22332

Answers (2)

Laurence Renshaw
Laurence Renshaw

Reputation: 652

Why not use the simple LOOKUP function?

I'll assume that the first blank space in Sheet1 (the CID for John Smith) is cell C2.

You can simply put this expression into that space:

=LOOKUP(C3,Sheet2!B$2:B$22001,Sheet2!A$2:A$22001)

and then copy/paste it into all the other blank cells.

Upvotes: 0

chrono
chrono

Reputation: 138

I don't think I could get vlookup to work either, because the data you're looking for is to the right of the data you want to return. I just learned how to do what you need using match() and offset() from this question. This function would need to go in the CID column in Sheet1:

    =OFFSET(Sheet2!$A$1,MATCH(D2,Sheet2!B:B,0)-1,0)

I used the absolute reference ("$A$1") so you could drag down the formula without that changing. I hope this helps!

Upvotes: 1

Related Questions