vlookup returns values that are in adjacent rows

[j_sun]

Hi,
I'm using vlookup and running into an issue where, if the formula doesn't find an exact match in the lookup range, it returns the value from the adjacent row. I really want to leave it blank if there is no match. Is there a setting in the formula I'm missing or is this one of the many annoying limitations/quirks of a vlookup?
If so, any suggestions on how to accomplish what I need with another formula?

Thanks!

 

[malbarki]

Hi j_sun
Try to use an IF and ISERROR functions it works for me

=IF(ISERROR(VLOOKUP(A2,G:H,2,FALSE)),"",(VLOOKUP(A2,G:H,2,FALSE)))

A2 = Look-up Value
G:H = Table array
2 = col_index_num

I hope it's what you're looking for

 

[Xiq]

Hi j_sun,

I believe you have missed a important step in the vlookup function. For an exact match you want to use "FALSE" for the [range_lookup] option.

This will return N/A# if it can't find a match. If you want to leave it blank, you can nest the function inside an IFERROR function. I.g. =IFERROR(VLOOKUP($B1,$A$1:$A$10,FALSE),"")

 

[json]

Hi j_sun,

I believe you have missed a important step in the vlookup function. For an exact match you want to use "FALSE" for the [range_lookup] option.

This will return N/A# if it can't find a match. If you want to leave it blank, you can nest the function inside an IFERROR function. I.g. =IFERROR(VLOOKUP($B1,$A$1:$A$10,FALSE),"")

This was my first thought as well....

 

[Xiq]

Hi j_sun,

To followup my last reply.

Here are some links you can read about VLOOKUP and another great function combo "INDEX + MATCH":
http://chandoo.org/wp/2010/11/02/how-to-lookup-values-to-left/
http://chandoo.org/wp/2008/11/19/vlookup-match-and-offset-explained-in-plain-english-spreadcheats/

 

[GFC]

I am not sure I understood you queation clearly...
However, instead of ISERROR() you could also use the older way
IF(ISNA(Vlookup(...));"lookupfromadjacentrow";Vlookup(...))

As @Xiq mentionned, do not forget the FALSE argument as last argument in the Vlookup function