Help with VLookup function

[JohnK]

Hi
I'm struggling with a VLOOKUP function where I'm referencing a
particular postal code to a table and the VLOOKUP is returning
inconsistent results.

The syntax is: =VLOOKUP(C8,AZ1:BB1578,3)

Where the postal code is located in C8, the lookup range is fromAZ1 to
BB1578, and the returned value should be from a cell in the 3rd column
of that range corresponding to (across from) the looked up postal code.
What is happening, is that certain postal codes are not looking up the
corresponding value in the 3rd column, but rather are looking up a
totally unrelated postal code and returning the value from the 3rd
column corresponding to that (incorrect) postal code.

I have tried changing the returned column value to the first column
just to see what I would get back. In one example I typed in the postal
code 4161, and the VLOOKUP went to the first column, where all of the
postal codes are located and returned the postal code of 3996. Whereas
4161 should have looked itself up and returned 4161.

I have tried putting in the TRUE and FALSE arguments in the syntax but
with no change to the result. Maybe I'm making some simple mistake
here. I would be grateful for any suggestions.

John from Oz

 

[Mangesh Yadav]

Is your table in AZ1:BB1578 sorted. If not please sort them and check the
results again.

Mangesh

 

[agarwaldvk]

Dear JohnK

What is happening is that it is returning the value corresponding t
the approximate match and not the exact match.

Specify the last parameter as FALSE and it will return the valu
corresponding to the exact match. Should an exact match not be found
an error value shall be returned.

The default value is TRUE (when not specified) indicating an inexac
search - this returns the largest value less than the search or looku
value.

Hence, you are getting what you are getting.

Hope this helps!


Best regards


Deepak Agarwa

 

[Max]

The syntax is: =VLOOKUP(C8,AZ1:BB1578,3)

Another possible cause of "strange" errors could be due to the table_array
not being fixed (i.e. "AZ1:BB1578" instead of "$AZ$1:$BB$1578") as the
formula is copied down from the starting cell

If the above formula is used in the starting cell, say in D8, which is then
copied down, take the precaution to put in D8 as:
=VLOOKUP(C8,$AZ$1:$BB$1578,3)

 

[JohnK]

Thanks for that. I know there'd be a simple explanation. I'm grateful
for your help.

JohnK

 

[Arvi Laanemets]

Hi

The formula as you typed it, is searching for nearest match for search
value, and is meaned for use with sorted source table. When you need to find
exact match, then the formula will be
=VLOOKUP(C8,$AZ$1:$BB$1578,3,0)
(Almost always it will be wise to use absolute references for lookup range
in this formula)
This formula search for exact match - when there is none, it returns an #NA
error

 

[Mangesh Yadav]

Hi JohnK

I assume that your table was not sorted (which indeed was your problem), and
appreciate the feedback

Mangesh