Lookup from bottom of range, ignoring bottom n no. of rows

Hi!
I have data in a column range where latest data is continuously added at the bottom of the column. The amount of records i.e. rows which can contain the data is not fixed, so I do not know in advance which row no. would have the latest data.

I am doing a lookup to match the data from the bottom of the range towards the top (so as to see the most recent result). Formula is as given below:

=LOOKUP(2,1/($F$1:$F$16=A3),$G$1:$G$16)

I now need to do a lookup, ignoring the last 5 rows from the range, i.e. most recent result ignoring the last 5 rows in the column. (I think it could be achieved with CountIf but am not able to get it to work).

Please note: I am specifically looking to ignore only the last 5 rows from the range, not looking for nth result.

Sample file attached.

Please try

=LOOKUP(2,1/(F1:INDEX(F1:F16,COUNT(G1:G16)-5)=A3),G1:G16)

Try this:

=LOOKUP(2,1/(($F$1:$F$16=A3)*(ROW($F$1:$F$16)<=MATCH(99^99,G:G)-5)),$G$1:$G$16)

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Please try

=LOOKUP(2,1/(F1:INDEX(F1:F16,COUNT(F1:F16)-5)=A3),G1:G16)

or

=LOOKUP(2,1/(F1:INDEX(F1:F16,COUNTA(G1:G16)-5)=A3),G1:G16)

Solution resolves the error.
Thanks once again Bo_Ry.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below.