LOOKUP formula behavior question.

[Tytalus]

Hi. So I just recently learned about the LOOKUP formula and it's use for finding the last non blank cell in a range.

For reference, I was looking at this explanation: https://exceljet.net/formula/get-value-of-last-non-empty-cell

So the formula that's returning the last non blank cell:
=LOOKUP(2,1/(F8:W8<>0),F8:W8)

My understanding is that because LOOKUP assumes an ascending order, when it can't find the value 2 it looks for the next lesser value, which means it's going to look from the end (assumed to be the greatest) to the beginning (assumed to be the least). I get that the logic behind how it's parsing the array and checking each element against a condition doesn't necessarily *have* to go from the greatest to the least, but allegedly that's what's happening here and is the reason that although *all* non-blank elements in the array are 1, it's going to find the last one).

But here's the thing, it's also the case that =LOOKUP(1,1/(F8:W8<>0),F8:W8) *also* returns the last non-blank element even though there is an *exact* match it could have returned earlier.

From this it *seems* that the behavior is to run the array element check against the condition from the end to the beginning for *all* cases, not just when there isn't an exact match.

Is this true? Does anyone know? Seems to me that if it is true, MS should just come out and explicitly say that somewhere. Indeed, in the link they explain that they deliberately chose a value that was greater than 1 because they seemed to think this would invoke the "start from the end and work backward" behavior, even though a value greater than 1 seems not to be necessary (1 itself will do fine).


Thanks

 

[shg]

LOOKUP and MATCH (with the last argument 1, meaning an ascending array) and both return the last match among equal values, e.g.,

=MATCH(1, {0,1,1,1,1,2}, 1)

... returns 5, not 2.

 

[Tytalus]

Interesting. Why isn't any of this (the direction in which a function checks an array) documented?

Your formula does return 5.

I also tried this though and something else happened:
=MATCH(1,1/(F8:W8<>0),0)
returned an value error.

So did this:
=MATCH(2,1/(F8:W8<>0),1)


Turning it into Array formula though I get real returns though, but the behavior differs.
{=MATCH(1,1/(F8:W8<>0),0)}
returns the *first* non-blank cell position.

But,
{=MATCH(2,1/(F8:W8<>0),1)}
returns the *last* non-blank cell position.

Seems like the last argument is controlling the direction of the evaluation.

 

[Tytalus]

Looks like
{=MATCH(1,1/(F8:W8<>0),1)}
*also* returns the *last* non-blank cell position.

 

[shg]

Interesting. Why isn't any of this (the direction in which a function checks an array) documented?

It is. Look at Help for the MATCH function.

 

[Tytalus]

No, it isn't.

https://support.office.com/en-us/ar...pId=xlmain11.chm60112&ui=en-US&rs=en-US&ad=US

What's documented are the requirements for ordering within the array for the function to return the *desired* result. What *isn't* documented is the *direction* the evaluation is happening in, which would be really useful to know. One of the first thoughts I had years (decades now) ago about VLOOKUP was when they described that it would return the *first* match I wanted to know if there was a formula that would return the *last* match.

 

[shg]

When matchtype = 1 or -1, MATCH does a binary search; the sign just controls whether it goes up or down after a comparison. The rest of the behavior is just a result of how binary searches work.

When it's 0, it does a linear search.

 

[Aladin Akyurek]

See:

https://www.mrexcel.com/forum/excel-questions/99621-lookup-value-unsorted-data.html#post492425

https://www.mrexcel.com/forum/excel-questions/310278-vlookup-multiple-matches-match-returned.html#post1523998