Xlookup
Website: https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929
The XLOOKUP function searches a range or an array, and then returns the item corresponding to the first match it finds. If no match exists, then XLOOKUP can return the closest (approximate) match.
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
As a Finance & Accounting Specialist, I used the xlookup for each month's UC Davis Health billing. There are different ATB ("Authorization to Buy") numbers, estimate numbers, media types, and ATB names. Our goal is to to see how much we are billing for each ATB number to make sure we are in budget and to check if we are billing enough for each ATB, which we can be summarized using a pivot table of the full report data. Also, if "N/A" returns, then I know there is either a new ATB that I would request more info from the media team.
However, as mentioned in its definition, the "XLOOKUP function searches a range or an array, and then returns the item corresponding to the first match it finds."
What I would do at work: Because we have different media types that can have the same estimate number, the xlookup will return the incorrect ATB number. To fix that, first we must filter both our reports (current month's billing & master file) to have the same media type before doing the xlookup. Second when doing the xlookup, I cannot select the entire column as an array. Instead, I must select only the cells I want after filtering to ensure the xlookup returns ATB names within that media type.
Since a workbook in the browser and Excel can be different according to https://support.microsoft.com/en-us/office/differences-between-using-a-workbook-in-the-browser-and-in-excel-f0dc28ed-b85d-4e1d-be6d-5878005db3b6: I decided to use the concatenate function to make sure I returned the correct ATB number when searching for BOTH the media type and estimate number as a criteria as shown below.
Below shows how there is the issue of xlookup returning only the first match it finds and the correct tab shows using the concatenate function to ensure the ATB number for that specific media type and estimate number is returned.
The XLOOKUP function searches a range or an array, and then returns the item corresponding to the first match it finds. If no match exists, then XLOOKUP can return the closest (approximate) match.
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
As a Finance & Accounting Specialist, I used the xlookup for each month's UC Davis Health billing. There are different ATB ("Authorization to Buy") numbers, estimate numbers, media types, and ATB names. Our goal is to to see how much we are billing for each ATB number to make sure we are in budget and to check if we are billing enough for each ATB, which we can be summarized using a pivot table of the full report data. Also, if "N/A" returns, then I know there is either a new ATB that I would request more info from the media team.
However, as mentioned in its definition, the "XLOOKUP function searches a range or an array, and then returns the item corresponding to the first match it finds."
What I would do at work: Because we have different media types that can have the same estimate number, the xlookup will return the incorrect ATB number. To fix that, first we must filter both our reports (current month's billing & master file) to have the same media type before doing the xlookup. Second when doing the xlookup, I cannot select the entire column as an array. Instead, I must select only the cells I want after filtering to ensure the xlookup returns ATB names within that media type.
Since a workbook in the browser and Excel can be different according to https://support.microsoft.com/en-us/office/differences-between-using-a-workbook-in-the-browser-and-in-excel-f0dc28ed-b85d-4e1d-be6d-5878005db3b6: I decided to use the concatenate function to make sure I returned the correct ATB number when searching for BOTH the media type and estimate number as a criteria as shown below.
Below shows how there is the issue of xlookup returning only the first match it finds and the correct tab shows using the concatenate function to ensure the ATB number for that specific media type and estimate number is returned.
Comments
Post a Comment