VLookup most recent entry

[Spicey_888]

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

[zbor]

Here, use this:

=LOOKUP(2, 1/($B$5:$B$10000=J5), $F$5:$F$10000)

[Spicey_888]

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

[zbor]

Sure...
LOOKUP formula

=LOOKUP(2, 1/($B$5:$B$10000=J5), $F$5:$F$10000)

will avaluate this array:

$B$5:$B$10000=J5

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

Then

1/($B$5:$B$10000=J5)

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:

=LOOKUP(2, 

(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

[Spicey_888]

That makes sense.

Why do we use the value 2...

=LOOKUP(2,

What does changing this number result in?

Cheers mate.

Spicey_888

[zbor]

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.