looking up the 2nd last number on a list

[sobrien]

I need a formula that looks up the 1st, 2nd last, 3rd last 4th last and 5th last number in an array from rows c:x. The list also has text in it "DNP" in some cells

For example in row 1 starting from C1

99 88 DNP 77 88 DNP DNP 76 DNP 66 79 80 (assume cells therafter are blank however will have data entered into them at a later date so cells C:X eventaully get filled over time.

Today though the answer to the formula would in cells z:ad would be

88 76 66 79 80 (i.e. the last 5 numbers from that row)

If next week another number gets added (say 101 gets added to the row) the answer in cells z:ad would be
76 66 79 80 101


Thanks

 

[DonkeyOte]

Quite a few possibilities - one being:

Excel Workbook
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD
1	99	88	DNP	77	88	DNP	DNP	76	DNP	66	79	80												88	76	66	79	80
2	99	88	DNP	77	88	DNP	DNP	76	DNP	66	79	80	DNP	101										76	66	79	80	101
3	1	2																									1	2
Sheet6

Formula in Z1 can be copied across results matrix (eg Z1:AD3 in the above)

 

[sobrien]

that is just awesome - you are a genius thank you!!!