Results 1 to 15 of 15
Thread: Lookup function problem

20131118, 20:17 #1
 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"?

20131119, 00:59 #2
 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 nonblank 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

20131119, 13:22 #3
 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.

20131119, 13:35 #4
 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?

20131119, 14:11 #5
 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.Last edited by skipro; 20131119 at 14:13.

20131119, 14:12 #6
 Join Date
 Jan 2001
 Location
 La Jolla, CA
 Posts
 1,468
 Thanks
 30
 Thanked 61 Times in 57 Posts
Try this approach (attached)
Last edited by kweaver; 20131119 at 14:20.

The Following User Says Thank You to kweaver For This Useful Post:
skipro (20131119)

20131119, 14:15 #7
 Join Date
 Dec 2009
 Posts
 212
 Thanks
 36
 Thanked 0 Times in 0 Posts
Screwed up, edited reply and attached example.

20131119, 15:43 #8
 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.

20131119, 15:58 #9
 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)))

20131119, 19:01 #10
 Join Date
 Dec 2009
 Posts
 212
 Thanks
 36
 Thanked 0 Times in 0 Posts
I get no return, blank cell.

20131119, 20:33 #11
 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?

20131120, 14:47 #12
 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.Last edited by skipro; 20131120 at 14:52.

20131120, 18:36 #13
 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)))

The Following User Says Thank You to kweaver For This Useful Post:
skipro (20131120)

20131120, 20:02 #14
 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.Last edited by skipro; 20131120 at 20:06.

20131120, 23:04 #15
 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.