getting data from cells above a lookup

[simico]

Hey guys,

I have researched many problems here in the past, but now I am finally posting one of my own.

I am using Excel 2007 on Windows XP. Columns: B = Years, C=Months, G= a percentage, with a few rows of data in between, and the whole thing is sorted ascending by year and month.

I goal is to find the last (most recent) percentage in G, and then back into each prior year's % for that same month. The formula I am using for the most recent data is "=LOOKUP(99^99,G:G)" and it works great!

My problem moving up 12 rows to get the last year's number. I tried "=OFFSET(LOOKUP(99^99,G:G),-12,0)" but it won't even let me hit Enter, so I know something is messed up.

Thanks in advance for all the help.

 

[Gerald Higgins]

Hi, welcome to the board.

Perhaps this

=INDEX(G:G,MATCH(99^99,G:G,1)-12,1)

 

[simico]

That worked!

Thank you so very much.

I'll try to make the next one more challenging