Lookup the maximum date of a record and return the result of a corresponding column

[MinniMaz]

I have two tables - Parent table listing asset numbers, and another table with records of services for that asset. I would like to lookup the asset number, search the maximum (last date) it was serviced in the records table, and return the comment that is in another column

eg:

Thank you

 

[Legacy 143009]

=INDEX(invoiced_range,MATCH(MAX((ref_range=customerRef)*runDate_rage),(ref_range=customerRef)*runDate_range,0))

This is an array formula. If you are office version is other than 365, you must paste the formula and press Ctrl+Alt Enter together. If you are using Office 365, then this should work also.

=LET(q,(ref_range=customerRef)*runDate_range,INDEX(invoiced_range,MATCH(MAX(q),q,0)))

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)