Return to Excel Formulas List

XLOOKUP Text – Excel & Google Sheets

Download Example Workbook

Download the example workbook

This tutorial will demonstrate how to use the XLOOKUP Function with text in Excel.

XLOOKUP Main

XLOOKUP with Text

To lookup a string of text, you can enter the text into the XLOOKUP Function enclosed with double quotations.

XLOOKUP by text 01

XLOOKUP with Text in Cells

Or, you can reference a cell that contains text.

XLOOKUP by text 02

XLOOKUP with Multiple Texts

If you want to look up a series of texts, enter the range reference into the formula, and a dynamic array “Spill” formula will be created.

XLOOKUP by text 03

The formula will “spill” to perform the calculations on the entire array of values.

 

Alternatively, you can reference a single cell, lock cell references, and copy the formula down.

XLOOKUP by text 04

Note: Make sure that the 2nd and 3rd arguments are in absolute references by adding dollar signs in front of the Column Letter and Row Number (or you can put the cursor in the reference while in the formula and press F4). This will fix the references while you’re dragging or copying the formula.

XLOOKUP with Last Text

By default, the XLOOKUP Function will search an exact match from the top of the list going down (i.e., Top-Down approach). We can reverse this by applying a value of -1 to the last argument: search_mode.

We can find the last occurrence of a text within a list by applying -1 to the last argument of the XLOOKUP Function.

XLOOKUP by text 05

XLOOKUP with EXACT Function

It’s not obvious from the previous examples, but the matching process of the XLOOKUP Function is case-insensitive. This means that text will match regardless of case (upper or lower).

We can use the EXACT Function to perform a case-sensitive match.

XLOOKUP by text 06

Let’s visualize how the formula above works:

XLOOKUP by text 07

The EXACT Function performs a case-sensitive comparison between two values.

In our example, an array is created, comparing the lookup value (see Column C) and each of the values from the lookup array (see Column B). The EXACT Function will return TRUE if the texts being compared are the same and FALSE if different (see Column D).

XLOOKUP by text 08

In our XLOOKUP Formula it looks like this:

XLOOKUP by text 09

Note: This will only work if there’s an array input (B3:B7 in the XLOOKUP formula). The EXACT Function will also return an array output as visualized in Column D.

The array output of the EXACT Function is then used as the lookup array for the XLOOKUP Function. We’ll search for the first occurrence of the TRUE value, which means an exact match, and return the corresponding status.

XLOOKUP by text 10

 

XLOOKUP with Partial Match

The matching process in the previous examples are all done by matching whole texts or phrases, but we can also perform a partial match in XLOOKUP.

XLOOKUP with Wildcards

The easiest method to perform a partial match is to use wildcards. We need to change the 5th argument: match_mode to 2 to enable this.

XLOOKUP by text 11

Note: The asterisk wildcard represents any number of any characters. By adding the asterisk to the front and end, we search for any text that contains “RS”.

XLOOKUP with SEARCH Function

Another way to perform a partial match is to nest a SEARCH Function inside the XLOOKUP.

In order for this to work, we must enclose the SEARCH Function using the ISNUMBER Function.

XLOOKUP by text 12

Let’s visualize how the formula works:

XLOOKUP by text 13

The SEARCH Function searches a text from another text. It will return a position number if it finds the lookup text. Otherwise, it will return an error.

Since we have an array input (B3:B7), it will perform the search for each of the values within the input array and will return an array output (Column C below).

XLOOKUP by text 14

 

Here’s how it looks in our formula:

XLOOKUP by text 15

The array output from the SEARCH Function is then feed to the ISNUMBER Function, which checks if the returned values of the SEARCH Function are numbers. If a value is a number, it will return TRUE. Otherwise, FALSE. (Please see Column D below)

XLOOKUP by text 16

XLOOKUP by text 17

Here’s how the result of the ISNUMBER looks in the formula:

XLOOKUP by text 18

We now have an array output of TRUE and FALSE values, which are used as the lookup array for the XLOOKUP Function. We’ll lookup for TRUE as the partial match.

XLOOKUP by text 19

 

XLOOKUP with FIND Function

The SEARCH and FIND Functions are almost the same function. One important difference is that the SEARCH Function is case-insensitive while the FIND Function is case-sensitive.

To perform a case-sensitive partial match, we’ll use the combination of the FIND Function and the ISNUMBER Function.

XLOOKUP by text 20

XLOOKUP Partial Match Problem

The previous partial matching methods discussed in the previous sections have one major flaw. They can’t distinguish an exact text match within a given phrase.

The partial matching process below can’t distinguish the term EPA to the “epa” from the word “Separate.”

XLOOKUP by text 21

 

XLOOKUP Exact Partial Match

If the words are separated by spaces, then we can add spaces to perform an exact partial match.

XLOOKUP by text 22

Note: We add spaces before and after E3, and we also do the same for the lookup array in B3:B7. This will bound the words with spaces at their front and end, which will be used to distinguish an exact matching of term within the phrases.

 

XLOOKUP Text Problem

One of the most common problems in text matching are unwanted spaces. Spaces are treated as part of texts. Therefore, if there are extra spaces, which are hard to notice, the XLOOKUP Function will return an error. Make sure that there are no unwanted spaces within the lookup value and the lookup array.

The lookup value (E3) in the example below has an extra space between the “Sub” and “2.”

XLOOKUP by text 23

XLOOKUP with TRIM Function

We can use the TRIM Function to solve the extra spaces. We can apply it to both the lookup value and the whole lookup array.

XLOOKUP by text 24

Note: The TRIM Function removes extra spaces between words until there’s a one space boundary between them, and any spaces before the first word and after the last word of a text or phrase are removed.