explanation of formula please

The values in A1:F1 must be sorted for this to work. If the LOOKUP function can't find the lookup_value (i.e. "zzzzzz..."), the function matches the largest value in lookup_vector (A1:F1 in this case) that is less than or equal to lookup_value.

Hence it finds the last item within the array.

A similar formula can be used for finding the last number in a numerical array, i.e.:

=LOOKUP(10E10,A1:F1)

where 10E10 and REPT("z",255) are just very large values.

Hope this helps.

Pete

The data has to be sorted, so your comments don't apply.

Suppose you have:

A, D, F, M

in those cells. Then the formula will return M, as it is the highest value less than "zzzzzz...".

Pete

Originally Posted by twiggywales

so if you had

A, D, F, M, B

why whould it show B not M as M is the largest value

or is it always the last cell

I meant to say that LOOKUP expects the data to be sorted, so in your example it will return B as that is the last cell which is less than "zzzz...", so yes, it will always return the last value in the list.

Hope this helps.

Pete

lookup works using a binary search so it splits the data in half, tests if the lookup value is greater than the last value in the first half - if so it throws away the first half and repeats the process with what's left (if not, it throws away the second half and repeats with the first half) this continues until it finds a match or runs out of data. normally for lookup (and vlookup or hlookup with fourth argument set to TRUE) to work as you want, you need to sort the data so that the splitting works, but here the formula is looking for a value greater than any value in the list so will always end up with the last value.