# 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?” ðŸ¤”

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
````=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

Watch the video

## Seeking Microsoft Certification?

No Huff! No Fluff! No Spam!