Return to Excel Formulas List


This tutorial will demonstrate how to combine the XLOOKUP and IF functions in Excel. If your version of Excel does not support XLOOKUP, read how to use VLOOKUP instead.

xlookup with if


XLOOKUP Multiple Lookup Criteria

There are a lot of ways to use the IF Function alongside the XLOOKUP Function, but first, let’s look at an example using the core element of the IF Function, the logical criteria.

One common example is performing a lookup with multiple criteria, and the most common solution to this is by concatenating the lookup criteria (e.g., F3&G3) and their corresponding column in the lookup data (e.g., B3:B7&C3:C7).

xlookup multiple lookup criteria 1

The above method works fine most of the time, but it can lead to incorrect results for conditions that involve numbers.


A more foolproof method is by creating an array of Boolean values from logical criteria.

xlookup multiple lookup criteria 2

Let’s walk through this formula:

Logical Criteria

First, let’s apply the appropriate condition to their corresponding columns by using the logical operators (e.g., =,<,>).

Let’s start with the first criterion (e.g., Student ID).

xlookup multiple lookup criteria step1

Repeat the step for the other criteria (e.g., Subject).

xlookup multiple lookup criteria step2

Array AND

Next, we perform the array equivalent of the AND Function by multiplying the Boolean arrays where TRUE is 1 and FALSE is 0.

xlookup multiple lookup criteria step3 array and

Note: The AND Function is an aggregate function (many inputs to one output). Therefore, it won’t work in our array scenario.


XLOOKUP Function

Next, we use the result of the Array AND as the new lookup array where we will lookup for 1 instead of the original lookup value.

xlookup multiple lookup criteria final

Combining all formulas above results to our original formula:


XLOOKUP Error-Handling with IF

Sometimes we need to check if the result of an XLOOKUP Function results in an error. A great way of doing this is by using the IF Function, which is also the best way of notifying us about the cause of the error.



Let’s first check if the XLOOKUP failed to find a match using the IF with ISNA Formula.

xlookup if with isna

Let’s walk through the above formula:

ISNA Function

First, let’s check for the #N/A Error, which basically means no match was found, using the ISNA Function.

xlookup if with isna isna function

IF Function

Next, let’s use the IF Function to check the result of the ISNA Function and return a message (e.g., “Product not found!”) if the result is TRUE. Otherwise, if the result is false, we’ll proceed with the calculation.

xlookup if with isna if function

Combining all formulas results to our original formula:



Another thing to check is if the result of XLOOKUP is blank. There are cases where blank means there’s no input yet, and therefore, we need to distinguish it from zero.

We’ll just replace the ISNA with ISBLANK to check for blank.

xlookup if with isblank

Let’s walk through the above formula:

ISBLANK Function

First, let’s check for blank using the ISBLANK Function.

xlookup if with isblank isblank function

IF Function

Just like with the previous scenario, we then input the result of the ISBLANK Function to the IF Function and return a message (e.g., “No Data!”) if TRUE or proceed with the calculation if FALSE.

xlookup if with isblank if function

Combining all formulas results to our original formula:



Another thing to avoid in calculations is accidental text input. In this case, we’ll use the IF with ISTEXT Formula to check for a text value.

xlookup if with istext

ISTEXT Function

First, we check if the output of the XLOOKUP Function is a text.

xlookup if with istext istext function


IF Function

Next, we check the result using the IF Function and return the corresponding message (e.g., “Invalid input!”) if TRUE or proceed to the calculation if FALSE.

xlookup if with istext if function


The final error-handling formula would be the combination of the previous IF Formulas, and we can do this by nesting them.

xlookup with ifs

As we notice above, the Nested IF Formula becomes more complicated as we add more conditions. A better way to approach this is by using the IFS Function.

ifs function

Note: The IFS Function can evaluate multiple sets of logical criteria. It starts from the first condition moving on to the next until it finds the first TRUE condition and returns the corresponding return value to it.


Let’s walk through the formula above:


We start with our first condition, which is the ISNA Function. If ISNA is TRUE, we return its corresponding value (e.g., “Product not found!”). Otherwise, we proceed to check the next condition.

ifs function isna xlookup



Since the first condition is FALSE in this scenario, we check the succeeding conditions until we find the first TRUE and return the corresponding value for the TRUE condition.

ifs function isblank xlookup

ifs function istext xlookup



Default Value

We can set a default value by setting the last condition as TRUE in case all the conditions are FALSE, which in our error-handling scenario, means that we can now proceed to the calculation without errors.

ifs function final

Combining all formulas above results to our original formula: