Last value lookup query

[Belfast Neil]

Hi All,

I have a large form pulling weekly scores from several other tables. From this collated table I have a formula that reads from the bottom up a column and finds the last call that wasn't an error. I'm using =LOOKUP(2,1/ISNUMBER(B$2:B$8),B$2:B$8)

The bit I can't figure out is how to get the value above this one and then subsequently the one above that. End goal is to have the most recent weekly score, the previous weeks' and the week previous to that.

[TABLE="width: 600"]
<tbody>[TR]
[TD]Week[/TD]
[TD]Score[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Week1[/TD]
[TD]#N/A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Week2[/TD]
[TD]#N/A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Week 3[/TD]
[TD]#N/A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Week 4[/TD]
[TD]41%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Week 5[/TD]
[TD]59%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Week 6[/TD]
[TD]#N/A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Week 7[/TD]
[TD]#N/A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Most recent[/TD]
[TD]59%[/TD]
[TD]=LOOKUP(2,1/ISNUMBER(B$2:B$8),B$2:B$8)[/TD]
[/TR]
[TR]
[TD]1 week previous[/TD]
[TD]?[/TD]
[TD]need to return 41%[/TD]
[/TR]
[TR]
[TD]2 week previous[/TD]
[TD]?[/TD]
[TD]need to return #N/A[/TD]
[/TR]
</tbody>[/TABLE]


Have tried offset but Excel is having none of it. I've attached a simplified version of what I'm trying to do. Any help greatly appreciated.

Regards,
Neil

 

[arthurbr]

=INDEX($B$2:$B$8,match(LOOKUP(2,1/ISNUMBER(B$2:B$8),B$2:B$8),$B$2:$B$8,0)-1) for 1 week previous and -2 for 2 weeks previous ?

 

[Belfast Neil]

Thanks Arthur, I don't even understand what your formula is doing but it works. Greatly appreciated.

 

[arthurbr]

You're welcome
Basically it looks for the last call with your formula, returning it's position in col B. Then 1 is subtracted from that position to find the value in the previous cell, then 2 to find the value in the next previous cell.
There is probably something shorter I suppose.