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

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

Mangesh



"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

Dear JohnK

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

Specify the last parameter as FALSE and it will return the value
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 inexact
search - this returns the largest value less than the search or lookup
value.

Hence, you are getting what you are getting.

Hope this helps!


Best regards


Deepak Agarwal


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

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)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

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

JohnK


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

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

Hi JohnK

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

Mangesh



"JohnK" wrote in
message ...

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

JohnK


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