Explain =LOOKUP(2,1/(A2:A10=D2),B2:B10)

Hi,

So I have got that formula by googling that gives the last instance of value of a look up but cant understand its functionality.

Someone plz explain. Also, other formula for achieving same result if any.

I have attached my file as well.

Thanks.

Hi and welcome to the forum

Here is an explanation given by Tony Valko on how "LOOKUP(2,1/" works.

As an alternative function you can use =INDEX(B2:B10,MATCH(D2,A2:A10,1))

http://www.pcreview.co.uk/forums/doe...-t3729826.html

For a list of items in A2:A10

D2: (the value to find)
E2: =LOOKUP(2,1/(A2:A10=D2),B2:B10))

The 1/(A2:A10=D2) phrase in that formula calculates an array of #DIV/0!'s and 1's.
Since we're trying to match 2 (which is larger than any of the calculated numeric values), the LOOKUP function ignores the errors and returns the value associated with the
LAST numeric item in the array.

Does that help?

Hi.

Thought I may as well through in my two cents' worth!

The breakdown is as follows:

=LOOKUP(2,1/(A2:A10=D2),B2:B10)

The part in red tests each of the values in A2:A10 against the condition of whether they are equal to the value in D2 or not. Hence, it will resolve to a string of TRUE/FALSE returns, i.e.:

=LOOKUP(2,1/({FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE}),B2:B10)

The clever part of this construction is the next bit, which involves taking the reciprocal of each of these Boolean TRUE/FALSE values, and exploiting the fact that, when coerced by any suitable mathematical operation, e.g. division, these Boolean TRUE/FALSE values are converted to their numerical equivalents (TRUE = 1, FALSE = 0), so that:

=LOOKUP(2,1/({FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE}),B2:B10)

becomes:

=LOOKUP(2,{#DIV/0!;#DIV/0!;#DIV/0!;1;1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!},B2:B10)

since 1/FALSE becomes 1/0, i.e. #DIV/0! and 1/TRUE becomes 1/1, i.e. 1.

Now, if the lookup_value is NOT found within the lookup_vector, LOOKUP returns the LAST value in that array which is less than the lookup_value. And, since we know that our array consists of only either 1s or #DIV/0!s, all that we have to do is choose a lookup_value which is greater than 1.

Any such value will do, though obviously there is some logic (and elegance) in choosing 2.

Since the last non-error value in the above array occurs in position 5 (highlighted):

=LOOKUP(2,{#DIV/0!;#DIV/0!;#DIV/0!;1;1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!},B2:B10)

Excel is instructed to return the value from the corresponding position in the range B2:B10, i.e. "ras", as you can see below:

{"sad";"ran";"pri";"dep";"ras";"siy";"fas";"asf";"aji"}

Hope that helps.

Regards

Thanks all for the replies, esp. Xor, his reply was detailed and in layman's term that I could follow...

Originally Posted by AlKey

Hi and welcome to the forum

Here is an explanation given by Tony Valko on how "LOOKUP(2,1/" works.

As an alternative function you can use =INDEX(B2:B10,MATCH(D2,A2:A10,1))

http://www.pcreview.co.uk/forums/doe...-t3729826.html

thanks for that simple and alternative use on index and match(1), i had always used index and match for reverse lookup and dynamic look ups.. learnt something new...

You're welcome and thank you for the feedback!

Please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools -> Mark thread as Solved).

I'm very familiar with this process, and if I wasn't, Ron and XOR LX's fine explanation above would settle that. However I believe I found a problem with it, though only tested in XL97 and XL03. If you specify full columns, you get #NUM!, and if you use the 1-isblank or NOT(ISBLANK alternate forms, you get the first, not the last, value in the column.

Below is a slight variation from the discussion above. This deals with finding the LAST VALUE in a column, rather than the last value matching a certain value.

(EDIT: Sorry! Earlier I omitted the <>"" in the next two formula )

lookup(2,1/(A:A<>""),A:A)
gives #NUM!

lookup(2,1/(A1:A65535<>""),A1:A65535)
is fine

lookup(2,1/(NOT(ISBLANK(A:A),A:A)
gives the value in A1, that is, the **top** value of the column, whereas I expected the BOTTOM one

lookup(2,1/(NOT(ISBLANK(A1:A65535),A1:A65535)
is fine

lookup(2,1/(NOT(ISBLANK(A1:A10),A1:A10)
is also fine for a reduced scope

I've run into problems with full columns in the past - I believe it was with SUMIF - so I sometimes have some gruesome looking arguments of B$1:E$65535 or such.

Whole column references don't work in earlier versions of Excel. Don't slow your sheet up with up with B$1:E$65535, ulesss you DO have 65 thousand rows!!!

Which versions did you test? BTW, I'm now testing your warning about wasted row checking!

Anything before and including) 2003.

I don't think you're at all right about that, unless you mean in the limited case of this thread's example (and I don't think you tested that either). If you can show me a formula that doesn't work in 2003 and prior, any formula, other than publicized additions such as the new SUMIFS, please share it. I and everyone have used A:A notation since the dark ages. (and SUMIF does in fact respect it)

It was sumproduct that I remembered having an issue with. That is not a version issue - it applies to all versions.

Further, your comment about calculations not respecting the used range appears to be only a [theoretical] issue for UDFs per the following. Excel functions are purportedly optimized with respect to Used Range.

Excel does document that array formulas did not use entire column references until 2007 though.

"Whole Column and Row References


An alternative approach is to use a whole column reference, for example $A:$A. This reference returns all the rows in Column A. Therefore, you can add as much data as you want, and the reference will always include it.

This solution has both advantages and disadvantages:

•Many Excel built-in functions (SUM, SUMIF) calculate whole column references efficiently because they automatically recognize the last used row in the column. However, array calculation functions like SUMPRODUCT either cannot handle whole column references or calculate all the cells in the column.


•User-defined functions do not automatically recognize the last-used row in the column and, therefore, frequently calculate whole column references inefficiently. However, it is easy to program user-defined functions so that they recognize the last-used row.


•It is difficult to use whole column references when you have multiple tables of data on a single worksheet.


•Array formulas in versions before Excel 2007 cannot handle whole-column references. In Excel 2007, array formulas can handle whole-column references, but this forces calculation for all the cells in the column, including empty cells. This can be slow to calculate, especially for 1 million rows."
https://msdn.microsoft.com/en-us/lib...ffice.14).aspx

So for those using 2007, that version only, Microsoft states that, for Array formulas (formulas that appear with { } ), both A:A and A$1:A$65000 (moreover A$1000000) are inefficient; a limited stated scope would be preferable for performance. At least that's their claim above.

(EDIT: you were on to something about limiting range with LOOKUP - the article later says that when you use FALSE (unlike the examples above), you should limit lookup ranges
"When you must use an exact match lookup, restrict the range of cells to be scanned to a minimum. Use dynamic range names rather than referring to a large number of rows or columns. Sometimes you can pre-calculate a lower-range limit and upper-range limit for the lookup."
)

(ONE MORE EDIT: Microsoft does add something about SUMIF: "For functions like SUM, SUMIF, and SUMIFS that handle ranges, the calculation time is proportional to the number of used cells you are summing or counting. Unused cells are not examined, so whole column references are relatively efficient, but it is better to ensure you do not include more used cells than you need. Use tables, or calculate subset ranges or dynamic ranges."

They're still claiming that only USED cells are considered; but that further limiting of the range could be profitable for performance.
)
-----------
LAST edit! Here is the final word, from the (IMO) world's leading Excel performance expert, Charles Williams. By the way, he co-wrote the article above as well, immensely boosting its credibility.

https://fastexcel.wordpress.com/2015...a-or-bad-idea/

"Conclusions

Excel’s sparse storage methods are efficient and allow for the use of whole column references and contiguous blocks of identical formats. But you need to be careful to minimize both the extent and complexity of the used range.
◾Formulas other than array formulas and SUMPRODUCT handle whole column references efficiently.
◾But avoid array formula and SUMPRODUCT usage of whole column references.
◾Non-array formulas using whole column references are much slower with large used ranges, and even slower with complex used ranges.
◾Formulas handle empty cells more efficiently than cells containing data.
◾Excel is optimized to handle contiguous blocks of identical formatting efficiently.
◾File size is not dependent on the size of the used range.
◾File size is largely dependent on the number of cells containing data/formulas and also on the number of non-contiguous formatted cells."

Mods I think I'm done on the topic in case it looks like it's yearning for a new thread. I just wanted to caution against using full column reference notation for the original thread contents...at least for the versions that I tested (and I expect it to hold for all versions, unless someone tests and reports that to be incorrect).

The thread will not be closed. It is already marked as solved, but must remain open in case anyone else wishes to add anything later on. Thanks for your understanding.

Okay. That's what I was aiming to do - add to the solution, adding a caveat, in case it improved the answer. Sorry that it strayed off a bit.

No problem! An interesting diversion.