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

1. ## 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.

2. ## Re: Lookup from bottom of range, ignoring bottom n no. of rows

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

3. ## Re: Lookup from bottom of range, ignoring bottom n no. of rows

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)

4. ## Re: Lookup from bottom of range, ignoring bottom n no. of rows

Thanks @Bo_Ry & @AliGW for your Prompt responses.

Both solutions work perfectly well.

6. ## Re: Lookup from bottom of range, ignoring bottom n no. of rows

Originally Posted by Bo_Ry

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

While trying both the above formulas, I encounter an error if the range for lookup contains non-numeric values. Could you please take a look again?

Sample file showing error attached.

Thanks.

7. ## Re: Lookup from bottom of range, ignoring bottom n no. of rows

=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)

8. ## Re: Lookup from bottom of range, ignoring bottom n no. of rows

Solution resolves the error.
Thanks once again Bo_Ry.

9. ## Re: Lookup from bottom of range, ignoring bottom n no. of rows

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.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1