How to use XLOOKUP in scenario where I want the latest date with AVAILABLE data?

Dear Mtarler,

Thank you so much for your time and providing this excel function!

If it's possible, may I clarify with a few follow-up questions:
1) What's the reason that you used INDEX function instead of XLOOKUP function? I actually haven't used INDEX function before

2) May you let me know your thoughts regarding your process for creating this formula?

The formula: =INDEX(Table1[people_fully_vaccinated_per_hundred],MAX((Table1[location]=A6)*ISNUMBER(Table1[people_fully_vaccinated_per_hundred])*ROW(Table1[location]))-ROW(Table1[#Headers]))

INDEX formula is INDEX(reference, row_num, [column_num], [area_num]).

- it makes sense the reference is the column with "People Fully Vaccinated Per Hundred"
- then for row_number, it looks like you used the maximum number in the "People Fully Vaccinated Per Hundred" column + Location = Chile country. but I don't understand the other elements.
- Why use "*" which is multiplication?
- I don't understand why if we're finding the maximum cell that is a number, why we need Row(Location)-Row(Headers) as well

Thank you kindly if you can provide more details in advance so I can further understand your logic and utilize the same process for other spreadsheets.

Kind regards,
Amy