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

[agarwaldvk]

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

[Mangesh Yadav]

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

Mangesh



"JohnK" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[JohnK]

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

JohnK

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

[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


--
Arvi Laanemets
( My real mail address: arvil<at>tarkon.ee )


"JohnK" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Mangesh Yadav]

Hi JohnK

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

Mangesh



"JohnK" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Mangesh Yadav]

Hi JohnK

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

Mangesh



"JohnK" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[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


--
Arvi Laanemets
( My real mail address: arvil<at>tarkon.ee )


"JohnK" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Mangesh Yadav]

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

Mangesh



"JohnK" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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

[Mangesh Yadav]

Hi JohnK

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

Mangesh



"JohnK" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[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


--
Arvi Laanemets
( My real mail address: arvil<at>tarkon.ee )


"JohnK" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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

[Mangesh Yadav]

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

Mangesh



"JohnK" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[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


--
Arvi Laanemets
( My real mail address: arvil<at>tarkon.ee )


"JohnK" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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

[Mangesh Yadav]

Hi JohnK

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

Mangesh



"JohnK" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Mangesh Yadav]

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

Mangesh



"JohnK" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Mangesh Yadav]

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

Mangesh



"JohnK" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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

[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


--
Arvi Laanemets
( My real mail address: arvil<at>tarkon.ee )


"JohnK" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Mangesh Yadav]

Hi JohnK

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

Mangesh



"JohnK" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Mangesh Yadav]

Hi JohnK

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

Mangesh



"JohnK" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[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


--
Arvi Laanemets
( My real mail address: arvil<at>tarkon.ee )


"JohnK" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Mangesh Yadav]

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

Mangesh



"JohnK" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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

[Mangesh Yadav]

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

Mangesh



"JohnK" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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

[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


--
Arvi Laanemets
( My real mail address: arvil<at>tarkon.ee )


"JohnK" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Mangesh Yadav]

Hi JohnK

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

Mangesh



"JohnK" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Mangesh Yadav]

Hi JohnK

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

Mangesh



"JohnK" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Mangesh Yadav]

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

Mangesh



"JohnK" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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

[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


--
Arvi Laanemets
( My real mail address: arvil<at>tarkon.ee )


"JohnK" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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

[Mangesh Yadav]

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

Mangesh



"JohnK" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Mangesh Yadav]

Hi JohnK

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

Mangesh



"JohnK" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[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


--
Arvi Laanemets
( My real mail address: arvil<at>tarkon.ee )


"JohnK" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[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


--
Arvi Laanemets
( My real mail address: arvil<at>tarkon.ee )


"JohnK" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Mangesh Yadav]

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

Mangesh



"JohnK" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Mangesh Yadav]

Hi JohnK

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

Mangesh



"JohnK" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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

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

[Mangesh Yadav]

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

Mangesh



"JohnK" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Mangesh Yadav]

Hi JohnK

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

Mangesh



"JohnK" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[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


--
Arvi Laanemets
( My real mail address: arvil<at>tarkon.ee )


"JohnK" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Mangesh Yadav]

Hi JohnK

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

Mangesh



"JohnK" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Mangesh Yadav]

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

Mangesh



"JohnK" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[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


--
Arvi Laanemets
( My real mail address: arvil<at>tarkon.ee )


"JohnK" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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

[Mangesh Yadav]

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

Mangesh



"JohnK" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Mangesh Yadav]

Hi JohnK

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

Mangesh



"JohnK" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[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


--
Arvi Laanemets
( My real mail address: arvil<at>tarkon.ee )


"JohnK" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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

[Mangesh Yadav]

Hi JohnK

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

Mangesh



"JohnK" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Mangesh Yadav]

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

Mangesh



"JohnK" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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

[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


--
Arvi Laanemets
( My real mail address: arvil<at>tarkon.ee )


"JohnK" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Mangesh Yadav]

Hi JohnK

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

Mangesh



"JohnK" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Mangesh Yadav]

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

Mangesh



"JohnK" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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

[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


--
Arvi Laanemets
( My real mail address: arvil<at>tarkon.ee )


"JohnK" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Mangesh Yadav]

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

Mangesh



"JohnK" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Mangesh Yadav]

Hi JohnK

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

Mangesh



"JohnK" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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

[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


--
Arvi Laanemets
( My real mail address: arvil<at>tarkon.ee )


"JohnK" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[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


--
Arvi Laanemets
( My real mail address: arvil<at>tarkon.ee )


"JohnK" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Mangesh Yadav]

Hi JohnK

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

Mangesh



"JohnK" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Mangesh Yadav]

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

Mangesh



"JohnK" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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

[Mangesh Yadav]

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

Mangesh



"JohnK" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[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


--
Arvi Laanemets
( My real mail address: arvil<at>tarkon.ee )


"JohnK" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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

[Mangesh Yadav]

Hi JohnK

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

Mangesh



"JohnK" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Mangesh Yadav]

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

Mangesh



"JohnK" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Mangesh Yadav]

Hi JohnK

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

Mangesh



"JohnK" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[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


--
Arvi Laanemets
( My real mail address: arvil<at>tarkon.ee )


"JohnK" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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

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

[Mangesh Yadav]

Hi JohnK

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

Mangesh



"JohnK" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Mangesh Yadav]

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

Mangesh



"JohnK" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[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


--
Arvi Laanemets
( My real mail address: arvil<at>tarkon.ee )


"JohnK" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Mangesh Yadav]

Hi JohnK

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

Mangesh



"JohnK" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[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


--
Arvi Laanemets
( My real mail address: arvil<at>tarkon.ee )


"JohnK" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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

[Mangesh Yadav]

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

Mangesh



"JohnK" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>