Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup on partially sorthed lists
Hi,
I am using a vlookup function without the false argument on a list (2400 items) that is mostly sorted, except for a few values (35) right at the bottom of the list that have a 0 value. I was under the understanding that the vlookup function stepped through the list until it came to a value that was greater than the lookup value at which time it brought back the relevant value from the nominated column to the right. On the data I have the function works OK until I try and lookup a value exceeding row +-2300. At this point the function brings back a 0 which is the data corresponding to one of the 0 value items. When I delete the 0 value items off the bottom of the list the vlookup works OK. The reason the data has a bunch of 0's on the end is that it is a dynamic list (made up of formulas that refer to other dynamic data). To replicate: Place 1 in B7 and C7 Place formula "=+B11+1" in B8 and copy down to B2401, copy also to column C Place value "0" in B2402 to B2436 and C2402 to C2436 Place value 2200 in D7 place "=VLOOKUP($D$7,$B:$C,2)" in E7 Change value in D7 to 2300 and a result of 0 is returned. The lookup works for all values less than 2200. Any assistance or explanation will be appreciated. Sean |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup on partially sorthed lists
Use a dynamic range that excludes the 0's from the lookup table:
InsertNameDefine Name: Range Refers to: =OFFSET(Sheet1!$B$7:$C$7,,,COUNTIF(Sheet1!$B$7:$B$ 3000,"0")) As you add new data to the end of the table replacing the 0's the range will automatically adjust. Then: =VLOOKUP(D7,Range,2) When you use the "sorted" range_lookup argument (you've used it by omission) and the range is not sorted (as yours wasn't) there's no telling what result you could get. Typically (but not always), the result will be the last entry of the lookup table. Biff "Sean" wrote in message ... Hi, I am using a vlookup function without the false argument on a list (2400 items) that is mostly sorted, except for a few values (35) right at the bottom of the list that have a 0 value. I was under the understanding that the vlookup function stepped through the list until it came to a value that was greater than the lookup value at which time it brought back the relevant value from the nominated column to the right. On the data I have the function works OK until I try and lookup a value exceeding row +-2300. At this point the function brings back a 0 which is the data corresponding to one of the 0 value items. When I delete the 0 value items off the bottom of the list the vlookup works OK. The reason the data has a bunch of 0's on the end is that it is a dynamic list (made up of formulas that refer to other dynamic data). To replicate: Place 1 in B7 and C7 Place formula "=+B11+1" in B8 and copy down to B2401, copy also to column C Place value "0" in B2402 to B2436 and C2402 to C2436 Place value 2200 in D7 place "=VLOOKUP($D$7,$B:$C,2)" in E7 Change value in D7 to 2300 and a result of 0 is returned. The lookup works for all values less than 2200. Any assistance or explanation will be appreciated. Sean |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup on partially sorthed lists
If you don't you false, then it must be sorted in ascending order, if not
then it will match the highest number smaller than your choice, if none are smaller then it will return #N/A. You could also do away with the zeros with an if statement. -- -John Northwest11 Please rate when your question is answered to help us and others know what is helpful. "Sean" wrote: Hi, I am using a vlookup function without the false argument on a list (2400 items) that is mostly sorted, except for a few values (35) right at the bottom of the list that have a 0 value. I was under the understanding that the vlookup function stepped through the list until it came to a value that was greater than the lookup value at which time it brought back the relevant value from the nominated column to the right. On the data I have the function works OK until I try and lookup a value exceeding row +-2300. At this point the function brings back a 0 which is the data corresponding to one of the 0 value items. When I delete the 0 value items off the bottom of the list the vlookup works OK. The reason the data has a bunch of 0's on the end is that it is a dynamic list (made up of formulas that refer to other dynamic data). To replicate: Place 1 in B7 and C7 Place formula "=+B11+1" in B8 and copy down to B2401, copy also to column C Place value "0" in B2402 to B2436 and C2402 to C2436 Place value 2200 in D7 place "=VLOOKUP($D$7,$B:$C,2)" in E7 Change value in D7 to 2300 and a result of 0 is returned. The lookup works for all values less than 2200. Any assistance or explanation will be appreciated. Sean |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup on partially sorthed lists
Thanks guys,
I guess that the answer is that if the list is not sorted that I can expect sporadic results. Thanks for the suggested work arounds. Much appreciated. Sean |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Drop-down lists in vlookup | Excel Discussion (Misc queries) | |||
VLookup a Vlookup | Excel Worksheet Functions | |||
Lookup Data in two seperate Spreadsheets | Excel Worksheet Functions | |||
VLOOKUP Limitations | Excel Worksheet Functions | |||
vlookup data hidden within worksheet | Excel Worksheet Functions |