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


--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )


"JohnK" wrote in
message ...

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


--
JohnK
------------------------------------------------------------------------
JohnK's Profile:
http://www.excelforum.com/member.php...o&userid=26498
View this thread: http://www.excelforum.com/showthread...hreadid=397653