In this lesson
Use XLOOKUP and FILTER, handle a missing product and keep the spill area clear.
Example
A price lookup and a filtered product list
Enter ProductID, Product and Price in A1:C4: P100 Keyboard 30, P200 Monitor 180 and P300 Dock 40. Put P200 in E2 and minimum price 35 in E5. Leave G2:I6 empty for the list returned by FILTER.
How it works
XLOOKUP uses exact matching by default and lets you specify a not-found result. If an ID occurs more than once, the first match may not be the record you intended; check the key for duplicates.
A dynamic array fills several cells from one formula. Enter it outside a table, leave room for its spill range and make changes in the anchor cell rather than the output cells.
Steps
Enter the lookup formula below in F2. Check that P200 returns 180. Change E2 to P999 to see Not found, then restore P200.
=XLOOKUP(E2,$A$2:$A$4,$C$2:$C$4,"Not found")English function names. Formula settings →
Enter the FILTER formula below in G2. It returns all three columns for records whose Price meets the threshold in E5.
=FILTER(A2:C4,C2:C4>=E5,"No matches")English function names. Formula settings →
Check the two results: P200 Monitor 180 and P300 Dock 40. Set E5 to 200 to see No matches, then restore 35.
Enter =SORT(UNIQUE(B2:B4)) in K2. Check that Dock, Keyboard and Monitor appear alphabetically. Leave its spill range clear of the filtered list.
In Excel 2016 or 2019, use =IFNA(INDEX($C$2:$C$4,MATCH(E2,$A$2:$A$4,0)),"Not found") for the price and an ordinary filter for the list. Those editions do not have XLOOKUP or FILTER.
If a recipient needs a fixed list, copy the results and Paste Values onto a labeled snapshot sheet. Keep the formulas in the source workbook for the next threshold change.
Try it yourself
- Append P400 Headset 25 in row 5 and explicitly extend the lookup and array source ranges to row 5. Explain why fixed ranges do not expand merely because you typed another record below them.
- Set E2 to P400 and E5 to 30. Predict the lookup and filtered list, then test a blocked spill: copy the FILTER formula from G2, clear G2, and enter temporary text in G3. Restore the formula in G2 to see #SPILL!, then clear G3 so the three-row result returns. You cannot type into G3 while it is part of an active spill.
Show expected result
- P400 returns 25 after the source ranges are extended. A minimum price of 30 returns Keyboard 30, Monitor 180 and Dock 40, excluding Headset 25.
- Temporary text within the needed output area causes a spill obstruction; clearing it allows the same anchor formula to return the list again.
If something goes wrong
- The lookup returns a price from the wrong duplicate ID.
- Filter ProductID and review the duplicate records. Resolve the source key before relying on the lookup; XLOOKUP does not choose which duplicate is correct.
- FILTER shows #SPILL! or Excel does not recognize the function.
- For #SPILL!, select the anchor and check for occupied output cells or a formula inside a table. If the function is unavailable, use the older-version lookup and ordinary filter described above.
Quick check
Try these questions after the exercise. Check the result in your app before marking the lesson complete.
Choose an answer for all 3 questions to check your understanding.
Optional: try it with Copilot
If you have Copilot, try this after the exercise. Keep the original file and compare its suggestions with your own result.
Check these product lookup and FILTER formulas. Show what happens for P999 and a blocked spill, and give the Excel 2019 INDEX/MATCH alternative.
More from Microsoft
These Microsoft guides cover the tools used in this lesson.
Loading your progress…