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
                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.

RELATED ARTICLES

WTF With XLOOKUP Function

DOWNLOAD WORKSHEET

Pin It on Pinterest

Share This

Spread the Good News!

Do me favour, please? Share this with your friends!

Thanks For Commenting!

Awesome for you to comment! Please share the post with your friends.