explanation of formula please

[twiggywales]

i saw this in another thread and don't understand how it works please can someone explain it.

=LOOKUP(REPT("z", 255), A1:F1) to return a text value in the right most cell (i have attached the sheet)


how does

lookup(zzzzzzzzzzzzzzzzzzzz,A1:F1) return the value required

interested.xlsx

[Pete_UK]

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

[bonny24tycoon]

Welcome to the forum



If I was able to help – PLEASE DO NOT FORGET to Click the small star icon at the bottom left of my post.

[twiggywales]

the function matches the largest value in lookup_vector (A1:F1 in this case) that is less than or equal to lookup_value.

Pete

so this would always be the rightmost cell with any value in even if there were cells further left with beginning with letters further up the alphabet. does largest value in lookup vector mean highest cell

i just cant quite grasp the logic behind this

[Pete_UK]

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

[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

[Marcol]

LOOKUP{} returns the last value less than the target if no exact match is found.
The data need not be sorted for this scenario.
It's unlikely you will ever match a string that contains 255 "z"s so you get the last cell with text.

As Pete suggests numeric values are treated differently, as are mixed data ranges

See this workbook for more examples.

[twiggywales]

ok thanks i think that you and pete cleared that up for me

cheers

:D

[Pete_UK]

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

[JosephP]

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.

[Limor_OP]

.............................