Lookup function problem

Topic

New Reply

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[/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”?

Reply | Quote

Replies

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

Reply | Quote

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.

Reply | Quote

I’m not quite following the “trailing zeros” part. Do you have a sample workbook you could post that shows what you want?

Reply | Quote

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.

Reply | Quote

Try this approach (attached)

Reply | Quote

Screwed up, edited reply and attached example.

Reply | Quote

kweaver,
Again thanks. I am amazed that there is no simpler way to do this.

Reply | Quote

I think this simpler array formula will work also: =INDEX(C:C,MAX((C:C0)*ROW(C:C)))

Reply | Quote

I get no return, blank cell.

Reply | Quote

Did you enter it using: CTRL+Shift+Enter?

Reply | Quote

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.

Reply | Quote

First off, you had something in C24…maybe a space…that’s why it didn’t catch it.

Reply | Quote

In example2, try this adjustment (still an array formula): =INDEX(C:C,MAX((0C:C)*(“”C:C)*ROW(C:C)))

Reply | Quote

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:C0)*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.

Reply | Quote