Index, Lookup and Offset functions

[TomOCJr]

I have watched the Mr Excel videos on how to have the last result in an row show in a cell using the Index, Lookup and Offset functions. All of the methods give men a result of 0.00. Note, the results that I want in the cell are answers from formulas.

Thank you so much..

Tom

 

[MARK858]

Try

=LOOKUP(2,1/(A3:Z3<>""),A3:Z3)

change the ranges to suit

 

[TomOCJr]

Try

=LOOKUP(2,1/(A3:Z3<>""),A3:Z3)

change the ranges to suit

Hello Mark 858, this works perfectly with this =LOOKUP(2,1/(C69:Z69<>""),C69:Z69) if I start with a new clean row and type in numbers like 100 200 300 etc. But my cells have a formula for adding 3 cells for a total. The total is what I want to show in my cell. Here is the formula with each cell having it's own column number over a period of one year: =SUM(D51:D53)

 

[MARK858]

Can you use the boards XL2BB add-in to post some sample data please so I can see what we are dealing with as formulas shouldn't be an issue

Book2
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
50
51	36	15	85					136
52
53
54
55
56
57
58					136
59
Sheet3

Cell Formulas
Range	Formula
K51	K51	=SUM(D51:F51)
H58	H58	=LOOKUP(2,1/(A51:V51<>""),A51:V51)

 

[TomOCJr]

Can you use the boards XL2BB add-in to post some sample data please so I can see what we are dealing with as formulas shouldn't be an issue

Book2
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
50
51	36	15	85					136
52
53
54
55
56
57
58					136
59
Sheet3

Cell Formulas
Range	Formula
K51	K51	=SUM(D51:F51)
H58	H58	=LOOKUP(2,1/(A51:V51<>""),A51:V51)

 

[MARK858]

Please use the boards addin rather than posting an image
a) so I can copy the data into Excel
b) so I can see where the formulas reside

What you have posted appears to show your data all in a column. Are you looking for the last cell in a column and not a row as you stated in post 1?

 

[TomOCJr]

Please use the boards addin rather than posting an image
a) so I can copy the data into Excel
b) so I can see where the formulas reside

What you have posted appears to show your data all in a column. Are you looking for the last cell in a column and not a row as you stated in post 1?

Sorry, this is confusing for me. Ment to send 2 images, here is the second.

 

[TomOCJr]

Sorry, this is confusing for me. Ment to send 2 images, here is the second.

No, I am looking for the last cell in the row. The 3 amounts add up to the total in row 50. This is the number I want in cell B50

 

[MARK858]

I still wish to see the data using the boards addin for the reasons given so I can test.

Instructions and download for XL2BB are below.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

 

[MARK858]

I'll pick up on this later in the day if no-one else has as I need some sleep (01:30 here)