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

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


Watch the video

Print Friendly, PDF & Email

Do you want to excel in Excel?


Seeking Microsoft Certification?

Every Week Receive Resources To Get you Closer to Your Goal!

No Huff! No Fluff! No Spam!

You are on your way to Excelling in Excel and Becoming Microsoft Certified!

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.