Forum Discussion
Index and Match with Max Function
Hello, I have a question in regards to index and match. So I'm referencing information from another spreadsheet which is my company's receiving log using the index and match function. The issue I...
I was busy these past few days so I'm sorry for this delayed reply. In the attached file, I assumed that the dates in your Receiving Log are recorded in chronological order, such that the formula in C2630 is:
=IFNA(LOOKUP(2,1/(
ReceivedOrders[P.O. No.]=[@1st]),
ReceivedOrders[Date]),"")
The result of the foregoing formula is 12/13/2019, as shown in the snapshot below:
Just out of curiosity, why doesn't the index and match function allow to implement a max in the match portion of the function?
- I presume my recommended formula returned your expected results. To satisfy your curiosity, MATCH returns the EXACT (0), NEXT LOWER (1), or NEXT HIGHER (-1) position for each of the the match_type arguments. LOOKUP always returns the LAST match, which is EXACTLY what you are LOOKING for, assuming the dates are recorded chronologically as is always normal in the course of events.
Incidentally, LOOKUP is my favorite function. Let me now declare that the LOOKUP is far better than both the debated choice between VLOOKUP and INDEX-MATCH.
Indubitably, the new and more powerful XLOOKUP simply improves the ability of my favorite LOOKUP.Sorry I need help one more time to get the syntax correct for referencing this function to the other sheet.
I attached both work books with the specified date and PO number.
Thank very much for your help!