+ Reply to Thread
Results 1 to 13 of 13

lookup formula problems

  1. #1
    Forum Contributor
    Join Date
    12-12-2012
    Location
    Los Angeles
    MS-Off Ver
    Excel 2010
    Posts
    448

    lookup formula problems

    I found a nifty formula that lookups the last value in a table based on a lookup value. However, I don't quite understand it. It is as follows: =LOOKUP(2,1/(A1:A10=C1),B1:B10)


    ID Value 1 40
    1 10
    2 10
    3 10
    4 10
    1 20
    3 10
    1 40

    What does the first number "2" refer to in the formula. And then what does the rest of the formula do? I'm trying to adapt for a spreadsheet that I have. Thanks.

  2. #2
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: lookup formula problems

    Well explained here at #012

    http://www.xldynamic.com/source/xld.LastValue.html
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  3. #3
    Forum Contributor bentleybob's Avatar
    Join Date
    02-27-2009
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2010
    Posts
    644

    Re: lookup formula problems

    Basically, the second part of the formula, that is 1/(A1:A10=C1), evaluates to 1 for each instance where an entry in Column A matches what's in C1. By specifying a 2 in the first part of the formula (or, actually, any number greater than one) you force the formula to find the last entry that meets the C1 criterion because it tries to find the number (in this case 2) and, if it doesn't find an exact match, goes down the list and finds the highest number less than 2. Since all entries are either 1 or #DIV/0!, it finds the last entry that evaluates to 1. If you specified 1 instead of 2, it would of course stop on the first matching entry.

  4. #4
    Forum Contributor
    Join Date
    12-12-2012
    Location
    Los Angeles
    MS-Off Ver
    Excel 2010
    Posts
    448

    Re: lookup formula problems

    Ok, will this work for text? =LOOKUP(2,1/(Actuals!F8:F2848=A39),'04-13'!G8:G2848)

    A39= Southwest Division F8-F2848 is the column with the values (there are numerous Southwest Divisions, I want the last one) and then when it finds the last one, I want the corresponding number in column G. It says #NA and I'm not sure why.

  5. #5
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: lookup formula problems

    Should work..Are your worksheets correct?

    'Actuals' and '04-13'?? or do you want to refer to just one worksheet?

    Also as per the formula Column F should contain the text 'Southwest division' as per what is inputted in A39

  6. #6
    Forum Contributor bentleybob's Avatar
    Join Date
    02-27-2009
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2010
    Posts
    644

    Re: lookup formula problems

    No, LOOKUP won't work across tabs. But you can have formulas in column F of "04-13" pull in the information from "Actuals" and then use the lookup just within "04-13".

  7. #7
    Forum Contributor bentleybob's Avatar
    Join Date
    02-27-2009
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2010
    Posts
    644

    Re: lookup formula problems

    I take it back. It does work across sheets. Check to be sure all your reference are correct. A39 needs to be in the current sheet, your lookup data needs to be in "Actuals" and the data you're looking for needs to be in "04-13" in your example.

    (see attached)
    Attached Files Attached Files
    Last edited by bentleybob; 06-11-2013 at 03:27 PM.

  8. #8
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: lookup formula problems

    Edit: Nevermind. All taken care of.
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  9. #9
    Forum Contributor bentleybob's Avatar
    Join Date
    02-27-2009
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2010
    Posts
    644

    Re: lookup formula problems

    ChemistB: my bad; spoke too soon.

  10. #10
    Forum Contributor
    Join Date
    12-12-2012
    Location
    Los Angeles
    MS-Off Ver
    Excel 2010
    Posts
    448

    Re: lookup formula problems

    Sweet. I got it to work!!!! FINALLY. I had written it incorrectly =LOOKUP(2,1/('04-13'!F3:F2848=A39),'04-13'!G3:G2848). So now that gets me the last value in the sheet. Is there any way that I can modify this to get the second to last, and third to last, etc? Please let there be haha.

  11. #11
    Forum Contributor bentleybob's Avatar
    Join Date
    02-27-2009
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2010
    Posts
    644

    Re: lookup formula problems

    Someone out there may come up with a more elegant solution for your follow-up, but if you can live with a helper column, try the attached (all in one tab to make it easier to develop). The helper column is highlighted in yellow.
    Attached Files Attached Files

  12. #12
    Forum Contributor
    Join Date
    12-12-2012
    Location
    Los Angeles
    MS-Off Ver
    Excel 2010
    Posts
    448

    Re: lookup formula problems

    Hmm, I'd prefer no helper columns because that sort of defeats the purpose of automating the report. I don't want to have to add a formula into the report (4-13) that I import. Although, could I just name my lookups East1 and then use the formula you put in there? I only need two things: the last value and the second to last. So couldn't I just use SW Division and =LOOKUP(2,1/(B1:B10=D1),C1:C10) AND then SW Div1 and then a modified form of =IFERROR(VLOOKUP($D$1&"|"&(COUNTIF($B$1:$B$10,$D$1)-1),$A$1:$C$10,3,FALSE),"")?

  13. #13
    Forum Contributor bentleybob's Avatar
    Join Date
    02-27-2009
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2010
    Posts
    644

    Re: lookup formula problems

    Well, it ain't pretty, but this gives you the last, 2nd to last, and 3rd to last. There may be a simpler way but this was [fairly] easy to derive.
    Attached Files Attached Files

+ Reply to Thread

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