Forum Discussion
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 with AVAILABLE data.
The problem that I'm experiencing is that I need to pull this data from a number of different countries and each country have different "Latest Date" so excel would look up the latest date however it is blank for Chile on 2/18/2022 because instead I would like Excel to capture the 89% fully vaccinated from 2/16/2022 instead for Chile for example. Is it possible for excel to skip the blank dates?
Thanks,
Amy
10 Replies
Hi AmyYang
This work for me. In cell B6 (Chile) or whatever cell in column B you can try:
= XLOOKUP( $A6&MAX( FILTER( Table1[date], (Table1[location]=$A6)* (Table1[people_fully_vaccinated_per_hundred]>0), "") ), Table1[location]&Table1[date], Table1[people_fully_vaccinated_per_hundred], "No")Best regards,
Carlos
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.
- 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
