Find data in last cell of a row

[bmckenna]

I need an excel formula, preferably no VBA, that finds the last cell's value in a ROW range.

 

[taigovinda]

I think it depends on whether the value is going to be text, or numeric. Check out this link, provided to me by DonkeyOte earlier today: http://www.xldynamic.com/source/xld.LastValue.html

 

[bmckenna]

Answer:

=INDEX(A:F,1,MATCH(LOOKUP(2,1/(1:1<>""),1:1),1:1,0))

Replace the "A:F" with your range of columns. And for the "1" following the "A:F" and all of the "1:1", replace them with the row number you want to search.

 

[taigovinda]

Hi Blake,

I don't think your formula is quite exactly what you are after. Look what happens if you add more data after columns A thru F:

Excel Workbook
	A	B	C	D	E	F	G
1	a	b	a	b	v	a	broken
2
3
4		your formula:	#REF!
5		formula as given in link:	broken
6		link formula, restricted to small range:	a
Sheet1

Excel 2003

Cell Formulas
Range	Formula
C4	=INDEX(A:F,1,MATCH(LOOKUP(2,1/(1:1<>""),1:1),1:1,0))
C5	=LOOKUP(2,1/(1:1<>""),1:1)
C6	=LOOKUP(2,1/(A1:F1<>""),A1:F1)

Even if you are not concerned with this possibility, your formula seems to be just doing extra work; I would suggest switching to the one in C6.

Tai

 

[bmckenna]

You bring up a good point, thanks Tai.

 

[Aladin Akyurek]

Answer:

=INDEX(A:F,1,MATCH(LOOKUP(2,1/(1:1<>""),1:1),1:1,0))

Replace the "A:F" with your range of columns. And for the "1" following the "A:F" and all of the "1:1", replace them with the row number you want to search.

What is in A1:F1 - text or numbers?