Multiple Criteria XLOOKUP

Learn how to use XLOOKUP with multiple criteria in Excel.

In our example, we will show how you can use the XLOOKUP Function with multiple criteria. The functionality is extremely useful and no longer requires multiple functions.

XLOOKUP is also able to handle arrays without having to use special key codes (Ctrl – Shift – Enter). Let’s look at the formula.

In the example below, we will use the criteria located in column I and array data from columns A, B,C, and D. Our result value will be pulled from column E. This follows the standard left to right nature of VLOOKUP.

XLOOKUP Multiple Criteria
Make sure you pay attention the "&" and "," in the formula.



Let’s look at the first part of the equation:

value1&value2&value3&value4  :  Make you separate with the "&" sign

I3&I4&I5&I6   :   We are selecting our criteria in column I

Next, we need to select the ranges that Excel will be searching through.

range1&range2&range3&range4  :  Again, make sure you use the "&" sign

A4:A13&B4:B13&C4:C13&D4  :  Here we select the columns as shown above.

Finally, we select the range where the result will be selected.

E4:E13  :  Excel is grabbing the result of $325,000

Leave a Reply

COUNTIF Function

The COUNTIF Function in Excel is extremely useful when dealing with large data sets. Here is a basic example of

Read More »

HLOOKUP Function in Excel

HLOOKUP Function Glossary What is HLOOKUP? HLOOKUP Example Exact Match vs Approximate Match (False vs True) HLOOKUP from another Workbook

Read More »
Scroll to Top