Looking up last 5 values

[MontyGs]

This is probably a stupid question but here it goes.... I want to find the last 5 values (non blank cells) that are set across a single row with 20 columns. And as I add more data it needs to continue to show the latest last 5 values.
I have the formula (below) to locate the last value in the set but for some reason I've gone brain dead on how to retrieve the last 4 behind it

Here's an example of what I got in the row thus far and what I'm looking for. "BC"= a blank cell
55 54 BC 63 53 BC BC 55 54 BC 53 BC 57 59
I used this generic formula =LOOKUP(2,1/(A2:T2<>""),A2:T2) to find the last value 59. I just can't figure out how to retrieve the other last 4 #'s (57,53,54,55) and so on as the list grows. And Yes, there will continue to be blank cells mixed in between the numbers.

I would greatly appreciate any assistance that is given! THANK YOU in advance!

[tim201110]

you can use LARGE as array formula

[taxcel rose]

Hi,

since you only have xl2003 I would use =INDEX($2:$2,Large(column($A$2:$T$2)*($A$2:$T$2<>""),Row(1:1)))
Hit [Ctrl]+[Shift]+[Enter] to enter it as an array function.

Michael

[shukla.ankur281190]

Assume you have A1:N1 with numbers and blank cells

Try

P1

Please Login or Register  to view this content.

Drag towards the cell

Check the attached file.

[Glenn Kennedy]

Taxcel rose:

=INDEX($2:$2,Large(column($A$2:$T$2)*($A$2:$T$2<>""),Row(1:1)))

You'll need to edit this. It doesn't take whole row references either....

[AZ-XL]

Hi

=INDEX($A$1:$U$1;LARGE(IF(NOT(ISBLANK($A$1:$U$1));COLUMN($A$1:$U$1);"");ROWS($A$4:A4)))

Range is A1:U1
if your data will go longer you should change the range

[taxcel rose]

=INDEX($2:$2,Large(column($A$2:$T$2)*($A$2:$T$2<>""),Row(1:1)))
You'll need to edit this. It doesn't take whole row references either....

Hm, I can't see any disadvantages due to referencing the whole row. There is no calculation based on this range within INDEX.

Michael

[AZ-XL]

Here is another solution.

Its an array formula as previous one but here you do not have to change range as new data added

=INDEX($1:$1;LARGE(IF(ISBLANK(OFFSET($A$1;0;0;1;LOOKUP(999999;$1:$1;COLUMN($1:$1))));"";TRANSPOSE(ROW(INDIRECT("1:"&LOOKUP(999999;$1:$1;COLUMN($1:$1))))));ROWS($D$4:D4)))

This one works only in numbers

[Glenn Kennedy]

INDEX($1:1... a whole row reference. not with Excel 2003....

[shukla.ankur281190]

Did you checked post #4 ???

[AlKey]

Try this:
Enter array formula in P1 and copy across to N1 and then down

**Must be entered with Ctrl+Shift+Enter key combination.

Please Login or Register  to view this content.

for those who use Excel 2010 version or higher may use this regular formula

Please Login or Register  to view this content.

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	55	54		63	53			55	54		53		57	59		55	54	53	57	59
2			12		47		48		11		8		15	21		48	11	8	15	21
3	17	45	17	19	24	49	32	32	89	77	74	32	11	12		77	74	32	11	12
4	5				4				3		2			1		5	4	3	2	1

Sheet: Sheet1

[MontyGs]

Thanks everyone for the replies and assistance!!! AlKey's formula seemed to work the best for what I need
But again I thank you all for your help!!!!

Have a Happy Memorial Day Weekend!