Index and Match with Max Function

[Dexlee46]

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'm having is I need to also nest the max function within this function. When I do this it prompts me to select my array, but this is determined through a matching purchase order. I'm just trying to pull back the latest date when the purchase order has been reconciled, this is applicable when there are multiple batch shipments of the same order since the order will have multiple shipments of the same part number and company. 

An example is we can receive part 123 in quantities of 50 on dates 1/2/2020, 3/3/2020, and 6/5/2020. On the receiving log I will need to pull the 6/5/2020 date as that will be the date when we received all of the parts from that specific order.  I attached a picture of the spreadsheets. The one with multiple colors, is my purchase order log, and contains the index and match function, and the other is the receiving log. I highlighted the columns I'm referencing which would be the date received, and the matching purchase order.

If someone could give me some insight on how to proceed, it would be much appreciated.

Thanks!

[#2]

Hi there's some useful examples here https://exceljet.net/how-to-lookup-first-and-last-match on getting the last match.

XLOOKUP will be able to search from the bottom up but it's not been released fully yet so doesn't help you right now.

[#3]

@Dexlee46 

Instead of images, please attach your sample Excel file.

[#4]

@Twifoo 

I attached both spreadsheets that I'm referencing. I deleted the columns that are applicable for simplicity. Book 2 is the sheet that will be returning values (purchase order log) and Book 3 is the sheet I'm conducting the index and match function (daily receiving log). Again I need to return the latest date, If you look at the bottom of book 2 (PO 1912017) I highlighted blue is the value I'm testing. In book 3 I added three orders for that PO with dates 12/11/2019, 12/12/2019, and 12/13/2019. The returning value in the "date received column" in book 2 should be 12/13/2019. But again I'm not sure how to nest the max function to do this within the index and match. I understand that the max must be nested after the match, but I don't believe excel will allow this, as the max needs an array to reference. But I need the array to be the dates that match with the corresponding look up value that is in the match. 

Let me know if this makes sense.

Thanks for your help!

[#5]

@Dexlee46 

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: 

[#6]

@Twifoo 

Thank you very much! 

[#7]

@Twifoo 

Just out of curiosity, why doesn't the index and match function allow to implement a max in the match portion of the function?

[#8]

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.

[#9]

@Twifoo 

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!

[#10]

@Dexlee46 

The data and structure of both workbooks you attached are now totally different from those you previously attached. Please specify the cells you need help in constructing the formula and explain the logic of each. Thereafter, I'll try to fathom my suggestion to you. 

[#11]

@Twifoo 

Sorry for the confusion. The cells will be the same the date received cells on the purchasing log will be will where I want to pull the dates from the daily receiving log. I will again need to match the part number and the PO number to pull the correct date, and again I will need to pull the latest date based on the PO number (if there are multiple batch orders).

Thank you again for your help!

[#12]

@Twifoo 

Hello,

Any chance you made any progress in regards to completing the function for the complete spreadsheet I sent over?

[#13]

@Dexlee46 

I believe the formula will be the same, as shown in the snapshot below: 

[#14]

@Twifoo 

Sorry I'm still confused on the syntax for referencing my receiving log. How is the syntax organized for referencing the log? 

The part of the formula I'm confused about is: Receiving[P.O. No.] & Receiving[Date].

Would you also want to jump on a skype call, in case I'm not explaining this very well.

Thanks.

[#15]

The table name is Receiving while those enclosed in brackets are Column Names. Such is the syntax for structured references to Excel Tables.

[#16]

@Twifoo

Just implemented it successfully.

Thank you very much for your help! 

[#17]

You’re very much welcome!

[#18]

@Twifoo 

Hey Robert,

Sorry I have an additional question for the problem involving finding the latest date from the receiving log. So here is the formula used currently:

=IFNA(LOOKUP(2,1/("Here"'Daily Receiving Log .xlsx'!Daily_Receiving_Log[P.O. No.]=[@1st]),'Daily Receiving Log .xlsx'!Daily_Receiving_Log[Date]),"")

So I'm matching only the PO number, but I will also need to match the part number.

My question is, for this formula I tried adding an add (where I have in bold "here") constraint right after the look up function, matching the function match the PO and the part number, but it didn't not work. Is there another way to create two constraints for this formula so I'm not getting incorrect date information?

Thanks.

[#19]

@Dexlee46 

In that case, you simply have to add another condition, as shown in the snapshot below: 

[#20]

@Twifoo 

Thank you very much!