Return to Excel Formulas List

XLOOKUP With If Statement – Excel & Google Sheets

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 IF Main Function

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(F3&G3,B3:B7&C3:C7,D3:D7)

XLOOKUP IF 001

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(1,(B3:B7=F3)*(C3:C7=G3),D3:D7)

XLOOKUP IF 002

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

=B3=$C$2

XLOOKUP IF 003

 

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

=D3=$E$2

XLOOKUP IF 004

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.

=C3*E3

XLOOKUP IF 005

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(1,F3:F7,G3:G7)

XLOOKUP IF 006

Combining all formulas above results to our original formula:

=XLOOKUP(1,(B3:B7=F3)*(C3:C7=G3),D3:D7)

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.

XLOOKUP IF with ISNA

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

=IF(ISNA(XLOOKUP(E3,B3:B7,C3:C7)),"Product not found!",F3/XLOOKUP(E3,B3:B7,C3:C7))

XLOOKUP IF 007

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.

=ISNA(XLOOKUP(E3,B3:B7,C3:C7))

XLOOKUP IF 008

 

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.

=IF(G3,"Product not found!",F3/XLOOKUP(E3,B3:B7,C3:C7))

XLOOKUP IF 009

Combining all formulas results to our original formula:

=IF(ISNA(XLOOKUP(E3,B3:B7,C3:C7)),"Product not found!",F3/XLOOKUP(E3,B3:B7,C3:C7))

XLOOKUP IF with ISBLANK

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.

=IF(ISBLANK(XLOOKUP(E3,B3:B7,C3:C7)),"No data!",F3/XLOOKUP(E3,B3:B7,C3:C7))

XLOOKUP IF 010

 

Let’s walk through the above formula:

ISBLANK Function

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

=ISBLANK(XLOOKUP(E3,B3:B7,C3:C7))

XLOOKUP IF 011

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.

=IF(G3,"No data!",F3/XLOOKUP(E3,B3:B7,C3:C7))

XLOOKUP IF 012

 

Combining all formulas results to our original formula:

=IF(ISBLANK(XLOOKUP(E3,B3:B7,C3:C7)),"No data!",F3/XLOOKUP(E3,B3:B7,C3:C7))

XLOOKUP IF 010

 

XLOOKUP IF with ISTEXT

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.

=IF(ISTEXT(XLOOKUP(E3,B3:B7,C3:C7)),"Invalid input!",F3/XLOOKUP(E3,B3:B7,C3:C7))

XLOOKUP IF 013

 

ISTEXT Function

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

=ISTEXT(XLOOKUP(E3,B3:B7,C3:C7))

XLOOKUP IF 014

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.

=IF(G3,"Invalid input!",F3/XLOOKUP(E3,B3:B7,C3:C7))

XLOOKUP IF 015

 

XLOOKUP with IFS

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

=IF(ISNA(XLOOKUP(E3,B3:B7,C3:C7)),"Product not found!",
  IF(ISBLANK(XLOOKUP(E3,B3:B7,C3:C7)),"No data!",
   IF(ISTEXT(XLOOKUP(E3,B3:B7,C3:C7)),"Invalid input!",F3/XLOOKUP(E3,B3:B7,C3:C7))
  )
 )

XLOOKUP IF 016

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(ISNA(XLOOKUP(E3,B3:B7,C3:C7)),"Product not found!",
ISBLANK(XLOOKUP(E3,B3:B7,C3:C7)),"No data!",
ISTEXT(XLOOKUP(E3,B3:B7,C3:C7)),"Invalid input!",
TRUE,F3/XLOOKUP(E3,B3:B7,C3:C7)
)

XLOOKUP IF 017

 

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:

ISNA

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.

=ISNA(XLOOKUP(E3,B3:B7,C3:C7))

XLOOKUP IF 018

ISBLANK and ISTEXT

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.

=ISBLANK(XLOOKUP(E3,B3:B7,C3:C7))

XLOOKUP IF 019

=ISTEXT(XLOOKUP(E3,B3:B7,C3:C7))

XLOOKUP IF 020

 

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.

XLOOKUP IF 021

Combining all formulas above results to our original formula:

=IFS(ISNA(XLOOKUP(E3,B3:B7,C3:C7)),"Product not found!",
ISBLANK(XLOOKUP(E3,B3:B7,C3:C7)),"No data!",
ISTEXT(XLOOKUP(E3,B3:B7,C3:C7)),"Invalid input!",
TRUE,F3/XLOOKUP(E3,B3:B7,C3:C7)
)