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

Option 1: Using IF Function filter exact data
Option 2: Combine criteria

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.

  1. Use a nested XLOOKUP to first return the approximate diameter column
  2. 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

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.


WTF With XLOOKUP Function