Lookup filtered data (again!)

[Steve@Rugby]

Hi,

Appoligies for asking a question that seems to have been asked and answered a hundred times before, I just cant make any of the answers to other posts work for me :-(

Im trying to lookup last occurance of a string text in column J, then return the value of column A.
Currently using this formula =IFERROR(LOOKUP(2,1/($J$2:J9=J10),$A$2:A10),"1st Occurrence"), this formula is working ok but I would really like to adjust/replace it with a formula that works when any column in the sheet has a filter applied.

The formula is entered in each used row of column N with vba each time a new row is added, the lookup vector is from row 2 up to the row before formula, so the example formula above is entered in cell N10.

I have attached (i hope!) a copy of the workbook i am testing with to this thread.

Thank you in advance
Steve

[Tony Valko]

I think this does what you're looking for...

Array entered**:

=IFERROR(LOOKUP(1E100,IF(SUBTOTAL(3,OFFSET(J$2,ROW(J$2:J2)-ROW(J$2),0)),IF(J$2:J2=J3,A$2:A2))),"1st Occurrence")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Format as Date

[Steve@Rugby]

Hi Tony,

Thanks a lot that works exactly as i need it to. Brilliant...

I only wish I understood the formula! Out of interest, can you explain the the lookup value "1E100" from your formula? Also why when i paste the formula it turns to "1E+100", i assume this is OK as it does work?

Thank you for your help
Regards
Steve

[Steve@Rugby]

Hi Tony,

Think i replied to your answer incorrectly (as in i replied to my post rather than yours) but hopefully this is ok.

How do i mark you reply as answer? Sorry it's my 1st post on here.

Steve

[Tony Valko]

1E100 (or 1E+100) is scientific notation for the very large number 1 followed by 100 zeros. Scientific notation is a "shorthand" method of expressing very long numbers.

What you're doing is returning the last (bottom-most) date that meets a condition.

In Excel dates are really just numbers formatted to look like dates. These numbers are the count of days since a base date. The default base date is Jan 1 1900. So:

Jan 1 1900 = 1
Jan 2 1900 = 2
Jan 3 1900 = 3
Jan 4 1900 = 4
Jan 5 1900 = 5
Jan 31 1975 = 27425
Dec 9 2013 = 41617

These numbers are also known as the date serial numbers. You can see them by entering some date in a cell then formatting the cell as General or Number.

In this application:

LOOKUP(1E100,range)

If the lookup value is greater than every number in the range then the formula will return the last number from the range.

We make sure that happens by using the gigantic lookup value of 1E100 (1 followed by 100 zeros).

[Steve@Rugby]

Hi Again Tony,

Thanks for the explanation, glad i asked now as i would never have worked out what was meant.

Thanks again
Regards
Steve

[Tony Valko]

You're welcome. Thanks for the feedback!