+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 15 of 18

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

  1. #1
    Forum Contributor
    Join Date
    01-21-2013
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    227

    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.
    Attached Files Attached Files
    If I've been of help, plz add reputation.

  2. #2
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365/2016
    Posts
    8,630

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

    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
    Last edited by AlKey; 10-14-2014 at 11:28 AM.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  3. #3
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016
    Posts
    6,850

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

    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?
    Ron
    Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  4. #4
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Excel 2010
    Posts
    6,665

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

    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
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  5. #5
    Forum Contributor
    Join Date
    01-21-2013
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    227

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

    Thanks all for the replies, esp. Xor, his reply was detailed and in layman's term that I could follow...
    Last edited by sakmsb; 10-14-2014 at 11:54 AM.

  6. #6
    Forum Contributor
    Join Date
    01-21-2013
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    227

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

    Quote Originally Posted by AlKey View Post
    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...

  7. #7
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365/2016
    Posts
    8,630

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

    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).

  8. #8
    Valued Forum Contributor
    Join Date
    11-15-2010
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2003
    Posts
    435

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

    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.
    Last edited by Oppressed1; 07-23-2017 at 06:37 AM.
    Level 1 answers to advanced questions deter advanced answerers

  9. #9
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    19,825

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

    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!!!
    Glenn



  10. #10
    Valued Forum Contributor
    Join Date
    11-15-2010
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2003
    Posts
    435

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

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

  11. #11
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    19,825

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

    Anything before and including) 2003.

  12. #12
    Valued Forum Contributor
    Join Date
    11-15-2010
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2003
    Posts
    435

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

    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)

  13. #13
    Valued Forum Contributor
    Join Date
    11-15-2010
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2003
    Posts
    435

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

    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

  14. #14
    Valued Forum Contributor
    Join Date
    11-15-2010
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2003
    Posts
    435

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

    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."
    Last edited by Oppressed1; 07-23-2017 at 09:40 AM.

  15. #15
    Valued Forum Contributor
    Join Date
    11-15-2010
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2003
    Posts
    435

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

    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).

+ Reply to Thread
Page 1 of 2 1 2 LastLast

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1