VLookup most recent entry

Hi All,

I have tried doing a bit of research into this issue on this forum and others but I havent been able to find an answer that works for me. I need a function such as VLookup to look through a table of data and return a 'Dip' value based on the 'Code' as can be seen in the attached spreadsheet.

The issue I have is I want excel to find the very last entry matching the lookup code and return the 'Dip' value for that. I could settle for something that goes off date except I may have multiple entries for that particular code on the same day, thus I require it to find just the last entry that matches.

Pretty much I just need the lookup function to work from bottom to top rather than top to bottom of the table array. Can anyone provide some assistance on this?

Vlookup Latest Value.xlsx

Cheers,

Spicey_888

Here, use this:

Formula:

Please Login or Register  to view this content.

Awesome,

Can you explain the syntax to this formula, Like how it works? I have a much larger and obviously more detailed sheet to apply it to.

Cheers

Sure...
LOOKUP formula

Formula:

Please Login or Register  to view this content.

will avaluate this array:

Formula:

Please Login or Register  to view this content.

As TRUE/FALSE values: Does each value in B column is equal to value in J5.

Then

Formula:

Please Login or Register  to view this content.

Will evaluate those TRUE/FALSE as (for example):

1/{TRUE, FALSE, FALSE, TRUE, TRUE, FALSE, FALSE....}
into
1, DIV/0, DIV/0, 1, 1, DIV/0, DIV/0...

LOOKUP formula will now seek for number 2 in this range:

Formula:

Please Login or Register  to view this content.

(Will work for any number greater than 1 but not less or equal).

Since it will never find it it will look until end of the range ignoring errors (this is crucial).

It will find last 1 and return corresponding value on that position in same size array of $F$5:$F$10000

You need to use value that theoretically will never be reached.

Since your biggest value is (if we ignore error) is number 1 (and 1 only in this case) then 2 is enough.
You would get SAME result if you would put number 3, 3000, 1.1, 123456789 or any other number bigger than 1.