VLOOKUP problem

[busdriver12]

I have to admit that in all the years I have been using Excel I have never had to use this function. However, I've looked at various examples over the years and had a loose understanding of it.

I have been presented with a problem at work for which I cannot find why it throws up a #N/A error. I'll keep it simple:

In a workbook I have a sign on sheet (called SignOn) consisting of 4 columns (Shift number, Driver name, Unique Staff ID and column for their signature).

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Shift[/TD]
[TD]Driver[/TD]
[TD]Staff ID[/TD]
[TD]Signature[/TD]
[/TR]
[TR]
[TD]4200[/TD]
[TD]J Bloggs[/TD]
[TD]?[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4201[/TD]
[TD]J Smith[/TD]
[TD]?[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

In a second sheet (called NAMES) - it is a simple table consisting of the drivers ID number in column A and their name in column B.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Staff ID[/TD]
[TD]Name
[/TD]
[/TR]
[TR]
[TD]2001[/TD]
[TD]J Blogs[/TD]
[/TR]
[TR]
[TD]2052[/TD]
[TD]J Smith[/TD]
[/TR]
</tbody>[/TABLE]


In the sign on sheet I want to type in the driver's name (Col B) and use VLOOKUP to insert the driver's Staff number from the NAMES sheet into Col C. I named a range (Data) in the NAMES sheet containing the table used in the VLOOKUP formula. formula:

=VLOOKUP(B2,Data,1,FALSE)

This throws up a #N/A error. The result I am expecting is the Staff ID for each name ("2001" and "2052" respectively) to be inserted into C2 and C3 of the SignOn sheet. I've used the Error Checking and Evaluate Formula in the Formula tab on the ribbon to try to pin down and it tells me that there data being looked up is not available (hence #N/A) to the formula, but as far as I can see it is there.

I can't help but feeling that there is a piece of logic or a crucial step I am missing.

Thanks in advance

 

[sandy666]

maybe try:

=INDEX($G$2:$G$3,MATCH(B2,$H$2:$H$3,0))

adjust ranges suitably

[Table="width:, class:head"][tr=bgcolor:#000000][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][th]

H​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

1​

[/td][td]Shift[/td][td]Driver[/td][td]Staff ID[/td][td]Signature[/td][td][/td][td][/td][td]Staff ID[/td][td]Name[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

2​

[/td][td]

4200​

[/td][td]J Bloggs[/td][td]

#N/A​

[/td][td][/td][td][/td][td][/td][td]

2001​

[/td][td]J Blogs[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

3​

[/td][td]

4201​

[/td][td]J Smith[/td][td]

2052​

[/td][td][/td][td][/td][td][/td][td]

2052​

[/td][td]J Smith[/td][/tr]
[/table]

 

[lrobbo314]

Vlookup only works if the thing you are searching for is in the first column of the lookup data. This is a good situation to use Index+Match. I turned the lookup data into a table and used the following formula.

=INDEX(Table6[Staff ID],MATCH(B2,Table6[Name],0))

You can always just put hard coded ranges in there like this.

=INDEX($J$1:$J:100,MATCH(B2,$I$1:$I$100,0))

, where column J is what you want to return and column I is what you are looking up.

 

[busdriver12]

Thanks for the info lrobbo314. I found the answer to my problem where you stated:

Vlookup only works if the thing you are searching for is in the first column of the lookup data. This is a good situation to use Index+Match. I turned the lookup data into a table and used the following formula.

The first sentence gave me a clue and what I did was to swap the columns in the lookup table on the NAMES sheet containing the lookup data. After doing this and using the formula in Col C of the SignOn sheet:

=VLOOKUP(B4,Names,2,FALSE)

produced the result I was expecting. I am also going to use the Index/Match suggestion as well as a learning exercise so I can better understand the mechanism of doing these sorts of lookups.

Many thanks!

 

[busdriver12]

Thanks for the reply sandy666.

I am going to implement your suggestion into my problem to help me understand an alternate approach to my problem.

maybe try:

=INDEX($G$2:$G$3,MATCH(B2,$H$2:$H$3,0))

adjust ranges suitably

Many thanks!

 

[sandy666]

you are welcome

have a nice day