Vlookup on partially sorthed lists

[Sean]

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

 

[T. Valko]

Use a dynamic range that excludes the 0's from the lookup table:

Insert>Name>Define
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

 

[Guest]

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.

 

[Sean]

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