CODEWITHSUNDEEP
excel
Zat
Zat

Reputation: 33

Excel Lookup Formula not working properly

I am working off of sheet1 that is set out like:

Colum One: (First Name) Colum Two: (Last Name) Colum Three: (Account ID) Etc.

I have another sheet2 within this file with just:

Colum One: (First Name) Colum Two: (Last Name) Colum Three: (Account ID)

I would like to be able to prepopulate the (First Name) and (Last Name) when I enter the (Account ID).

I have tried doing this in many different ways, and I have searched the web and tried coping other example but still cannot get it to work.

I had the formula as "=vlookup(c3,Sheet2!$A$2:$C$10,1,False)" to do the (First Name) and then changing the 1 to a 2 for (Last Name).

It is returning an error and I cant find a website that helps.

Has anyone got an idea.

Thanks,

Upvotes: 0

Views: 96

Answers (1)

BruceWayne
BruceWayne

Reputation: 23285

Vlookup() is going to be tricky, since you can only search right. You can use Index/Match instead:

In your sheet2, A2 (where your "First Name" cells start), you can do:

=INDEX(Sheet1!B$2:B$9,MATCH($C2,Sheet1!$C$2:$C$9,0))

Where:

Sheet1!B$2:B$9 is the range of your First Names that you want to use to populate the other sheet.
Sheet1!$C$2:$C$9 is the range of all the Account IDs. $C2 is the Account ID on Sheet2.

Then, you can drag that right to B2, and it should grab the Last Names.

Upvotes: 3

Related Questions