VLOOKUP won't return desired value

[mathias1979]

Ok, so I have a lookup table that looks something like this:

Month, 1, 2, 3,...
Altona, 35, 49, 52,...
Bliss, 21, 37, 47,...
...

My function looks like this:
VLOOKUP("Altona",A14,4)

So the value returned should be '52'...but I get #N/A. However, if "Bliss" is the lookup value, the formula properly returns the value '47'. Excel for some reason doesn't want to match the name "Altona". I've double checked that spelling matches....I've double checked the cell references...I'm completely stumped. The entire formula is a bit more complex, in that the column to be returned (in this case, 4) is determined by its own HLOOKUP function. But when I step through the formula steps...I reach the last step without problem, so I assume the rest of the formula is working properly. Any ideas why Excel may not want to lookup the name "Altona" properly for me?

 

[xlhelp]

Have you checked for a space after Altona?

Member- AAAA Association Against Acronym Abusers

 

[yogia]

Hi Mathias:

Month, 1, 2, 3,...
Altona, 35, 49, 52,...
Bliss, 21, 37, 47,...
...

My function looks like this:
VLOOKUP("Altona",A1:D4,4)

Since your LookUP column is not in ascending order, you need to use FALSE or 0 as the 4th argument, as in ...

VLOOKUP("Altona",A14,4,0)

I hope this helps.


Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[AnotherHiggins]

Yogi:

That won't prevent the formula from working and it won't return the #N/A error. Try it and see.

#N/A indicates that no match is found. As xlhelp suggests, the most likely culprit is an extra space in the cell that contains Altona (D2 in the example).

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[yogia]

Hi John:

Good Call ... Thanks!

You are absolutely right that as long as Altona were to be found in the LookUp column, the formula may give an incorrect result but the result wouldn't be #N/A.

So the OP not only has to use the 4th argument as FALSE or 0 as I suggested in my post, but also has to check that the LookedUp value indeed exists in the LookUp column.

Now with the possibility of a leading or trailing space with Altona in the LookUp Column, I can use the formula ...

=VLOOKUP("*Altona*",A14,4,0) to get the correct result of 52

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[mathias1979]

actually, yogia's solution of adding '0' for the fourth argument works. I didn't even think to try it because I didn't think it was needed in this case, and all my other lookup values were working fine. thanks for the help guys!