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...
The last record for which there exists data is given by
= XLOOKUP(
country,
IF(fullyVaccinated,location),
fullyVaccinated,
"No records",
0,
-1)For this to correspond to the latest date, the data needs to be sorted ascending by date (which it is).
If 'country' is the array of countries for which you wish to see results the formula will spill to return results for the entire list.
Not to rely on source sorting it could be
=XLOOKUP(A3,
SORTBY( IF(Table1[people_fully_vaccinated_per_hundred], Table1[location]),
Table1[date], -1),
SORTBY( Table1[people_fully_vaccinated_per_hundred],
Table1[date],-1),
"no records" )