Forum Discussion
How to use XLOOKUP in scenario where I want the latest date with AVAILABLE data?
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" )PeterBartholomew1 Hi Peter - thank you so much for providing the XLOOKUP and IF formula! I did not know we can use the formula like that!
May I follow-up regarding the IF function within the XLOOKUP:
1) First, lookup value is country (e.g. Chile)
2) We use IF function and include % Fully Vaccinated Data, why? Because we didn't put in value if true or value if false, so what is the purpose of this?
3)Then afterwards I understand we want the return array back to Xlookup formula
Thank you,
Amy
1) First, lookup value is country (e.g. Chile): Correct
2) We use IF function and include % Fully Vaccinated Data, why? Because we didn't put in value if true or value if false, so what is the purpose of this?
Rather than looking for the country in the original list, I look for it in an array that is modified to show FALSE if there is no data. That prevent the lookup from retuning lines with no data.
3)Then afterwards I understand we want the return array back to Xlookup formula
Yes, that is the new array to be searched.
