# Thread: Formula to extract last filtered item

1. I have a spreadheet the contains either a N or U in column C from row 8 unwards. I use a filter to show all the U's or N's.

I need a formula that will extract the last item in column C from row 8 onwards and placing this in H159

Your assistance will be most appreciated

Thanks

Howard

2. You probably need a code to place the input directly in H159
OR
=LOOKUP("Z",C8:C65536) in H159 will return the last value from the specified range.

Thanks for the help. does the "Z" in the formula represent the last item in the lookup or what does it represent?

Regards

Howard

4. Originally Posted by HowardC

Thanks for the help. does the "Z" in the formula represent the last item in the lookup or what does it represent?

Regards

Howard
Since there are only 2 character in your data, I use "Z" to make excel understand to look this first & if not found, put the last value from the range.

5. For me, the question is slightly different: how to return the content of the last visible row of a filtered area. If this is the correct question, then I would suggest the following:
1) Create a new column (that you can hide later on), with the formula =ROW() on each row. It simply returns the row number. let's say it is the column D.
2) On the cell D159, put the formula =SUBTOTAL(4, D1158) which returns the maximum of the filtered value of the column D. This means it returns the number of the last visible row of the filtered range.
3) In the cell A159, put the formula =OFFSET(A\$1, \$D\$159 - 1, 0) which will return the value in the column A of the last filtered row. Extend the formula to all the useful cells of the row 159.
4) Finally, simply put in H159 the formula: =C159

6. Finding the last filtered entry is difficult and possibly easier with a custom function
However,IF you want to find the entry in the last cell in say column C that has either
a U or an N in it then putting this formula somewhere would work I think
(Well it did for me anyway)

NOTE..These are ARRAY Formulas and so must be entered with

SHIFT CTRL ENTER

=INDEX(C8:C2000,MAX(IF(C8:C2000="U",ROW(C8:C2000), IF(C8:C2000="N",ROW(C8:C2000),0))),1)

You would need to adjust the end row to allow for your data

If you wanted to make it flexible to parameters fore the U and N then replace these with
Absolute Cell Refs to where the U and N could be types

eg

=INDEX(C8:C2000,MAX(IF(C8:C2000=\$H\$157,ROW(C8:C200 0),IF(C8:C2000=\$H\$158,ROW(C8:C2000),0))),1)

#### Posting Permissions

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