Excel LOOKUP formula to return multiple values (all matches)
Excel LOOKUP formula to return multiple values (all matches)
Practice file: https://excelkid.com/xlookup-all-matc...
How to XLOOKUP returns all matches (lookup multiple matches)
If we are talking about lookup functions in Excel, everyone knows that XLOOKUP is a Swiss knife in Excel. It is good to know that it works like other lookups; it returns the first matching record corresponding to a lookup value. However, here is the workaround with the Excel FILTER function.
The FILTER function uses two required and one optional argument to get all matches:
- array: the array that contains the possible matches
- include: filters an array based on the criteria
First, create two named ranges to simplify the formula. Select the range, locate the name box, and enter a descriptive name for a given range. The second argument, [include], will find and extract all matching records from the “data” range. You can use the FILTER function to filter data based on custom criteria. In the example, we want to return the matching rows where the total score equals 281. The formula returns multiple results, and Excel will (like other dynamic array functions in Excel) spill all records that meet the criteria.
Chapters:
0:00 Intro
0:08 XLOOKUP can not return all matches
0:15 Create named ranges
0:31 Use the FILTER function to get all matching records
0:58 Thank you
#msexcel #excel #exceltips #exceltricks #excelformula #excelfunctions #productivity #datacleaning #xlookup
Watch video Excel LOOKUP formula to return multiple values (all matches) online without registration, duration hours minute second in high quality. This video was added by user Excel Bootcamp 19 October 2023, don't forget to share it with your friends and acquaintances, it has been viewed on our site 17,69 once and liked it 3 people.