VLOOKUP returning values with the most recent date

In B3 and copy down.

=INDEX($G$3:$G$13,SMALL($H$3:$H$13=MAX(IF($F$3:$F$13=A3,$H$3:$H$13),ROW($G$3:$G$13)-2),ROW($A$1)))

Perhaps in B3

Please Login or Register  to view this content.

and pull down as needed

It uses the LOOKUP function in it's vector form.
The first vector's denominator looks for all values where the date is the largest one given the ID value in range F
It returns a an array of 0,0,0,1, which after division produces an array like ( DIV/O,DIV/0,1,DIV/0,etc...).
LOOKUP looks for a 2, which it can't find so chooses the largest value smaller than 2, which is 1 and selects the value of the second vector corresponding to that 1

Ir's a good idea to use the Evaluate formula tool to see what's going on

Thanks so much for you help!! Have a good day

Moderators Note: As per Forum Rule #12, don't quote whole posts unless necessary-- it's just clutter...Thanks.

Hello,hope you are well. You have helped me the other day in regards to my question on how to vlookup items by the most recent date and on my sample file everything worked perfectly fine. When i'm trying to apply this formula to my real file it doesn't seem to work the same way and I just can't figure out what the issue could be. Could you please take a quick look to see what i'm doing wrong, i'd really appreciate your help.

I'm attaching a file (i've cut out sensitive fields). I've applied the formula to column B on sheet POS. The value is supposed to be taken from column D on OBS sheet.

e.g. Sheet POS row 10, pulls 29 for Location id 54. On sheet OBS, 29 is not the value for the most recent date of those available for location id 54.

Please help.

Moderators Note: As per Forum Rule #12, don't quote whole posts unless necessary-- it's just clutter...Thanks.

Take a look and see if this helps you.

You are welcome.

Thanks for the reb*

Originally Posted by chowe

Hello.

My first post, but this has been driving me mad.
Not an expert at Excel but using the LOOKUP formula given above I've managed to get the attached sheet almost working.
I've two sheets that use external data and need the longest due date (column E) to be populated in the order ready (column B).

Hope somebody can help, I'm sure it's a simple task for someone in the know.

Thanks.

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread