This post is inspired by Dave Bruns’s post on Two-way approximate match multiple criteria.
Having read Dave’s article I thought… “Is there an easy way of doing this using XLOOKUP instead of INDEX/MATCH?” 🤔
The aim was to return the feed rate (D6:H16) given the material, hardness and diameter.
There are 2 tricks to getting the function working correctly.
- Use a nested XLOOKUP to first return the approximate diameter column
- Either use IF function to filter necessary hardness row based on the Material or combine the two criteria and use a combo-lookup array
=XLOOKUP(K5&K6,//Return Combo multiple criteria
material&hardness,//Return Combo lookup array
The outer XLOOKUP Combines the material and hardness criteria and creates a combo lookup array of Materials & Hardness.
NB: This ability of XLOOKUP to instantly create and search a combo array makes it suitable for multiple criteria search. It is extremely hard to do this with any other lookup function
Also, unlike the INDEX/MATCH function, you do not need to sort the diameter and hardness data in ascending order.
Watch the video