Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Help with VLookup function
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 |
#2
|
|||
|
|||
Is your table in AZ1:BB1578 sorted. If not please sort them and check the
results again. Mangesh "JohnK" wrote in message ... Hi I'm struggling with a VLOOKUP function where I'm referencing a particular postal code to a table and the VLOOKUP is returning inconsistent results. The syntax is: =VLOOKUP(C8,AZ1:BB1578,3) Where the postal code is located in C8, the lookup range is fromAZ1 to BB1578, and the returned value should be from a cell in the 3rd column of that range corresponding to (across from) the looked up postal code. What is happening, is that certain postal codes are not looking up the corresponding value in the 3rd column, but rather are looking up a totally unrelated postal code and returning the value from the 3rd column corresponding to that (incorrect) postal code. I have tried changing the returned column value to the first column just to see what I would get back. In one example I typed in the postal code 4161, and the VLOOKUP went to the first column, where all of the postal codes are located and returned the postal code of 3996. Whereas 4161 should have looked itself up and returned 4161. I have tried putting in the TRUE and FALSE arguments in the syntax but with no change to the result. Maybe I'm making some simple mistake here. I would be grateful for any suggestions. John from Oz -- JohnK ------------------------------------------------------------------------ JohnK's Profile: http://www.excelforum.com/member.php...o&userid=26498 View this thread: http://www.excelforum.com/showthread...hreadid=397653 |
#3
|
|||
|
|||
Dear JohnK What is happening is that it is returning the value corresponding to the approximate match and not the exact match. Specify the last parameter as FALSE and it will return the value corresponding to the exact match. Should an exact match not be found, an error value shall be returned. The default value is TRUE (when not specified) indicating an inexact search - this returns the largest value less than the search or lookup value. Hence, you are getting what you are getting. Hope this helps! Best regards Deepak Agarwal -- agarwaldvk ------------------------------------------------------------------------ agarwaldvk's Profile: http://www.excelforum.com/member.php...o&userid=11345 View this thread: http://www.excelforum.com/showthread...hreadid=397653 |
#4
|
|||
|
|||
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 -- |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
Hi
The formula as you typed it, is searching for nearest match for search value, and is meaned for use with sorted source table. When you need to find exact match, then the formula will be =VLOOKUP(C8,$AZ$1:$BB$1578,3,0) (Almost always it will be wise to use absolute references for lookup range in this formula) This formula search for exact match - when there is none, it returns an #NA error -- Arvi Laanemets ( My real mail address: arvil<attarkon.ee ) "JohnK" wrote in message ... Hi I'm struggling with a VLOOKUP function where I'm referencing a particular postal code to a table and the VLOOKUP is returning inconsistent results. The syntax is: =VLOOKUP(C8,AZ1:BB1578,3) Where the postal code is located in C8, the lookup range is fromAZ1 to BB1578, and the returned value should be from a cell in the 3rd column of that range corresponding to (across from) the looked up postal code. What is happening, is that certain postal codes are not looking up the corresponding value in the 3rd column, but rather are looking up a totally unrelated postal code and returning the value from the 3rd column corresponding to that (incorrect) postal code. I have tried changing the returned column value to the first column just to see what I would get back. In one example I typed in the postal code 4161, and the VLOOKUP went to the first column, where all of the postal codes are located and returned the postal code of 3996. Whereas 4161 should have looked itself up and returned 4161. I have tried putting in the TRUE and FALSE arguments in the syntax but with no change to the result. Maybe I'm making some simple mistake here. I would be grateful for any suggestions. John from Oz -- JohnK ------------------------------------------------------------------------ JohnK's Profile: http://www.excelforum.com/member.php...o&userid=26498 View this thread: http://www.excelforum.com/showthread...hreadid=397653 |
#7
|
|||
|
|||
Hi JohnK
I assume that your table was not sorted (which indeed was your problem), and appreciate the feedback :) Mangesh "JohnK" wrote in message ... Thanks for that. I know there'd be a simple explanation. I'm grateful for your help. JohnK -- JohnK ------------------------------------------------------------------------ JohnK's Profile: http://www.excelforum.com/member.php...o&userid=26498 View this thread: http://www.excelforum.com/showthread...hreadid=397653 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
format cell based on results of vlookup function | Excel Worksheet Functions | |||
Using ~ in VLookup function | Excel Worksheet Functions | |||
Vlookup w/Date Function | Excel Worksheet Functions | |||
How can I see an example of the vlookup function in excel? | Excel Worksheet Functions | |||
Regarding IF function or vLOOKUP function | Excel Worksheet Functions |