XLOOKUP Two-way approximate match multiple criteria

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?” 🤔

XLOOKUP Two-way approximate match multiple criteria — Option 1: Using IF Function filter exact data

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

**Use the Second XLOOKUP to spill the Approximate Diameter Column**

=XLOOKUP(K5&K6, //Return Combo multiple criteria
           material&hardness, //Return Combo lookup array
                XLOOKUP(K7,diameter,data,,-1),,-1)

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.