Results 1 to 15 of 15
  1. #1
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts

    Lookup function problem

    I am using a simple lookup function to find the last value in a column. This works fine if values are numbers [1,2,3] but does not work if values are from a sum [A2+B2].
    Ex Column A is numbers: this works.
    =Lookup(9e+107,a1:a5)

    If Column B is numbers and Column C is the sum of A+B then:
    =Lookup(9e+107,c1:c5) It does not return the last value, it returns "0"

    How can I get it to return the last value or "Sum"?

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,468
    Thanks
    30
    Thanked 61 Times in 57 Posts
    Try this: =INDEX(C:C,COUNTA(C:C)) for the last value in column C.

    Or: =OFFSET(C1,COUNT(C:C)-1,0)

    This won't work if there is an empty cell in the range.

    Another way, if there are blank cells, uses an array formula. Here are the elements and the final expression (a Chip Pearson approach):

    =MAX((B:B<>"")*(ROW(B:B)))

    returns the row number of the last non-blank cell in the B column. (entered as an array formula).

    However, using this in the ADDRESS function as: =ADDRESS(MAX((B:B<>"")*(ROW(B:B))),COLUMN(B:B)) [also an array formula]
    you'll get the cell reference (e.g., maybe $B$8)

    Then, combining all of this with INDIRECT:

    =INDIRECT(ADDRESS(MAX((B:B<>"")*(ROW(B:B))),COLUMN (B:B))) [also an array formula]
    you'll get the data from the last entry. PHEW

  3. #3
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts
    Thanks weaver,

    If column "C" has trailing "0"s [at the end] because no values have yet been entered in columns "A,B this far down", how would I get the last value in "C" that is not >0 [or a value that corresponds to a sum "A+B" where a number has been entered in "A"]?
    I know how to do this by avoiding "0"s in "C" by using null [""], but what can I incorporate into [=Lookup(9e+107,C:C)] to disregard these trailing "0"s if they exists and return only the last "valid" number?
    I am thinking of in terms of an IF >0, type of function to go with the Lookup function to disregard zeros in "C".
    Can this be done?
    I am trying to develop stronger Excel skills.
    Thanks.

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,468
    Thanks
    30
    Thanked 61 Times in 57 Posts
    I'm not quite following the "trailing zeros" part. Do you have a sample workbook you could post that shows what you want?

  5. #5
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts
    kweaver,
    Workbook enclosed.
    Cx=Ax+Bx
    C12:C17 = 0 because no entries in A,B12:A,B17

    G2 is =Lookup(99e+107,C:C)
    therefore G2 sees C17 [0] last entry when I want to return value in G11 [24].
    I need G2 to disregard the 0s after the last "valid" return of Cx [C11 in this example].
    I need a - "Consider all values in C:C that >0 then Lookup last number [>0]"
    or "In C:C, disregarding all 0s, now Lookup last number/value of remaining cells [>0]".
    If (C:C >0), Lookup(9e+107,C:C) - type of function(s).
    I hope I have been clearer this time.

    Thanks.
    Attached Files Attached Files
    Last edited by skipro; 2013-11-19 at 14:13.

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,468
    Thanks
    30
    Thanked 61 Times in 57 Posts
    Try this approach (attached)
    Attached Files Attached Files
    Last edited by kweaver; 2013-11-19 at 14:20.

  7. The Following User Says Thank You to kweaver For This Useful Post:

    skipro (2013-11-19)

  8. #7
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts
    Screwed up, edited reply and attached example.

  9. #8
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts
    kweaver,
    Again thanks. I am amazed that there is no simpler way to do this.

  10. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,468
    Thanks
    30
    Thanked 61 Times in 57 Posts
    I think this simpler array formula will work also: =INDEX(C:C,MAX((C:C<>0)*ROW(C:C)))

  11. #10
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts
    I get no return, blank cell.

  12. #11
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,468
    Thanks
    30
    Thanked 61 Times in 57 Posts
    Did you enter it using: CTRL+Shift+Enter?

  13. #12
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts
    Yes I entered them as an array.
    Strange happenings here. In the example I sent originally it works but when I put it my work book. I get a blank cell. I made another example [#2] where the return cell is blank [N3, O3].
    In example #2; K3, F3, G3 are using your first suggestions, and they work. N3, O3 are with the new formula and they do not work.

    Both examples attached.
    Attached Files Attached Files
    Last edited by skipro; 2013-11-20 at 14:52.

  14. #13
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,468
    Thanks
    30
    Thanked 61 Times in 57 Posts
    In example2, try this adjustment (still an array formula): =INDEX(C:C,MAX((0<>C:C)*(""<>C:C)*ROW(C:C)))

  15. The Following User Says Thank You to kweaver For This Useful Post:

    skipro (2013-11-20)

  16. #14
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts
    New formula seems to work but I do not understand why the previous one works in some columns but not all.
    =INDEX(C:C,MAX((C:C<>0)*ROW(C:C)))
    Please look at attached example 3. Column B,D,E,G,H work. Columns & F do not.
    Same formula, similar entries.
    Why doesn't C & F work?
    This is a head scratcher..

    What is different in the latest formula compared to the previous one [above]?

    Thanks.
    Attached Files Attached Files
    Last edited by skipro; 2013-11-20 at 20:06.

  17. #15
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,468
    Thanks
    30
    Thanked 61 Times in 57 Posts
    First off, you had something in C24...maybe a space...that's why it didn't catch it.

Posting Permissions

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