Make vlookup pick the latest date or comments in the list

[crazysniper]

Hi Everyone,

How can i make vlookup pick the most recent comments in the list without having to arrange the dates in descending?

I have a comments field in the main tab (see excel sheet attach) which fetch information from the comments tab. I am using vlookup for this, however, vlookup picks the first match it finds, so if i don't sort the dates into descending it will pick the wrong comment.

Thanks in advance.

[samba_ravi]

E2=INDEX(Comments!$D:$D,MAX(INDEX((Comments!$B$2:$B$7=MAX((Comments!$A$2:$A$7=$A2)*Comments!$B$2:$B$7))*ROW(Comments!$B$2:$B$7),0)))
try this and copy towards down

[crazysniper]

Hi nflsales,

thanks for the response.

i added the formula, as per your suggestion, unfortunately it didn't quite work. i added 2 new comments with thesame date and for differrent identifier. see attach excel file.

[FlameRetired]

I may be confused since the latest upload. Does this look like what you want?

Row\Col	A	B	C	D	E
1	LineIdentifier	CustomerName	FruitBeingOrdered	Kilo	Latest Comment
2	1	XYZ	Apple	5	10/03/15 -- Order Booked
3	2	ABC	Apple	6	10/03/15 -- Fixed
4	3	DEF	Banana	7	07/03/15 -- Order is Invoiced. Customer to make a payment
5	4	XYZ	Banana	8	03/03/15 -- Waiting for Customer to send a new PO

The formula I used array-entered in E2 of the main page and filled down is:

Please Login or Register  to view this content.

Array enter means the formula must be committed by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

[crazysniper]

@FlameRetired

Yes that works. Thank you so much.

But if don't mind explaining the code I would love to understand it in order for me to learn. I have done nested functions before, but not to this extent..

Thanks once again.

[FlameRetired]

=crazysniper;4012508@FlameRetired

........I would love to understand it in order for me to learn.......

Good!

The breakdown of this formula (and others) can get a bit wordy. There is a word limit on posts. I won't challenge it.

I'll go through a few of the initial steps and tell you about some tools you can use to teach yourself. Self education is the best education.

The heart of this formula is the two nested "IF"s......this one......

IF($A2=Comments!$A$2:$A$12,Comments!$B$2:$B$12)

If you will highlight / select just one of these in the formula bar and hit the F9 key you will see.....

Please Login or Register  to view this content.

Each of those numbers are the underlying values of the dates corresponding to all of the 1s (in this case) in column A of the Comments sheet. BTW dates are integers. Formatting is cosmetic. Inside of this array there is no formatting.

The other initial IF is inside of the first (innermost) MAX. Selecting that you would see.

Please Login or Register  to view this content.

These are compared against one another as

Please Login or Register  to view this content.

passed as the first argument of another IF that assigns index numbers in its second argument.

Sometimes the comparisons are TRUE sometimes FALSE.

Please Login or Register  to view this content.

The TRUES get assigned the index numbers. The FALSE "play through".

What you need is the last date and therefore last / highest index number. These are passed outward (Excel evaluates nests from the inner most to the outer most) to the next MAX. MAX does two things. It evaluates the maximum (the 8) and coerces it out of that sea of FALSE and other numbers in the array.

That 8 is passed to INDEX which holds the target range in its first argument. The 8 corresponds with the desired item's row number in that range and returns that item.

You can follow these steps with most formulas applying two methods in Excel. One is "Evaluate formula". You will find it in the Formula menu. You can also assign it to the Quick Access Toolbar. The other is to sequentially apply the F9 key as we have here.

There is more detail to this formula that I didn't cover, but using these two devises will take you through all of those details.

Beyond that spend time on the Forum, visit other sites (there is a resource at the top of the list of new Forum posts called "Sticky: Excel Useful Links". Explore that. Read other posts; take formulas apart. Start with some topic / function you are interested in. Have fun.

Hope that helps.