Forum Discussion
Solved
How to use XLOOKUP in scenario where I want the latest date with AVAILABLE data?
Hello, May I ask, if you could open the attached excel, currently I am trying to use XLOOKUP formula to look up for example, country Chile's latest vaccination percentage from the LATEST date wi...
AmyYang here is a 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]))
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
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