CODEWITHSUNDEEP
excelexcel-formulavlookup
Yanayaya
Yanayaya

Reputation: 2184

Can I use VLOOKUP to join data in my Excel document?

My excel document has two sheets, sheet 1 has the names of car manufacturers on it and sheet 2 has those same names with their relative ID (this is a database that it's taken from).

Sheet 1

Name
Ford
Accura
Holden

Sheet 2

Id    Name
1     Ford
2     Accura
3     Holden

I'd like to be able to lookup sheet to and match the strings to get the Id of the car manufacturer from it. I tried using VLOOKUP but I cannot get it to work.

Let's assume that on Sheet1 the name column is D2 (not including title of course) and that the sheet I want to lookup is called "CarMakes" in which the data is B3:C17 (not including titles)

I wrote my vlookup in the following way:

=VLOOKUP(D2,CarMakes!B3:C17,2,TRUE)

However, this gives me a result of '#N/A' so I think my approach is wrong. Can anyone help me with this and is vlookup the right way to do it? Also, can I perform these sam operations by connecting to my database which is in Azure?

Upvotes: 0

Views: 74

Answers (2)

Harun24hr
Harun24hr

Reputation: 36840

You need INDEX()/MATCH()

=INDEX(CarMakes!$A$2:$A$4,MATCH(A2,CarMakes!$B$2:$B$4,0))

If you have Excel365 then can try XLOOKUP().

=XLOOKUP(A2,CarMakes!$B$2:$B$10,CarMakes!$A$2:$A$10)

enter image description here

Upvotes: 1

Milan Paunovic
Milan Paunovic

Reputation: 1

Use $ before coordinate

=VLOOKUP(D2,CarMakes!$B$3:$C$17,2,TRUE)

That should help.

Upvotes: 0

Related Questions