Explanation

The goal is to look up and retrieve employee information in a table that contains unique id values in the first column. The VLOOKUP function is straightforward to use with data in this format, but you can easily use the XLOOKUP function as well. See below for a detailed explanation of both approaches. For convenience, id (H4) and data (B4:E104) are named ranges .

VLOOKUP function

VLOOKUP is an Excel function to get data from a table organized vertically . Lookup values must appear in the first column of the table passed into VLOOKUP. The data to retrieve is specified by column number, and the generic syntax for VLOOKUP looks like this:

=VLOOKUP(lookup_value,table_array,column_index_num,range_lookup)

The syntax looks a bit scary in this form, but it is quite simple in practice. The formulas below show how to get the first name, last name, and email address with VLOOKUP:

=VLOOKUP(id,data,2,0) // first
=VLOOKUP(id,data,3,0) // last
=VLOOKUP(id,data,4,0) // email

In these formulas, id is the named range B4 (which contains the lookup value), and data is the named range B4:E104 (the data in the table). Next, we have the column number, which is a number that indicates the column from which we want to retrieve data, where the first column is column 1 and contains lookup values. We provide 2 to retrieve the first name, 3 to retrieve the last name, and 4 to retrieve the email address. Notice that this is the only thing changing in the formulas above, every other input remains the same. Finally, we have the last value, which is zero (0). We use zero in these formulas to tell VLOOKUP to only perform an exact match. In “exact match mode” VLOOKUP will only match an id value exactly. If an id is not found, VLOOKUP will return the #N/A error. With the number 869 in cell H4, the formulas above return the results seen in the worksheet in column H:

=VLOOKUP(id,data,2,0) // returns "Julie"
=VLOOKUP(id,data,3,0) // returns "Irons"
=VLOOKUP(id,data,4,0) // returns "j.irons@abc.com"

To enter formulas like this, start with the first formula, then copy it down and change the column number as needed. The named ranges will behave like absolute references and will not change when the formula is copied to a new location.

Note: VLOOKUP will perform an approximate match by default. This is a dangerous default in this case because the data is not sorted, and there is a good chance that VLOOKUP will return an incorrect result. It is therefore important to require an exact match by using FALSE or 0 for the last argument , which is called “range_lookup”. More information here .

XLOOKUP function

Another way to solve this problem is with XLOOKUP , a modern upgrade to the VLOOKUP function. This minimal syntax for XLOOKUP looks like this:

=XLOOKUP(lookup_value,lookup_array,return_array)

Unlike VLOOKUP, we don’t give XLOOKUP the entire table . Instead, we provide separate ranges for lookup_array and return_array . The formulas to look up the first name, last name, and email address with XLOOKUP look like this:

=XLOOKUP(id,B5:B104,C5:C104) // first
=XLOOKUP(id,B5:B104,D5:D104) // last
=XLOOKUP(id,B5:B104,E5:E104) // email

Notice the only value changing is the range provided for return_array , which varies according the to information we want to retrieve. We use C5:C104 for the first name, D5:D104 for the first name, and E5:E104 for the email address. The value for lookup_value is the named range id (H4). See below for information on how to adapt this formula to use the existing named range data (B4:E104).

Note: Normally, I would use absolute references to make the formulas easier to copy, but I have left these ranges relative to make them a bit easier to read.

XLOOKUP with a named range

If we had named ranges for ids , first , last , and email defined, we could use them in XLOOKUP like this:

=XLOOKUP(id,ids,first) // first
=XLOOKUP(id,ids,last) // last
=XLOOKUP(id,ids,email) // email

Similarly, if data was a proper Excel Table , we could use structured references like so:

=XLOOKUP(id,data[Id],data[First]) // first
=XLOOKUP(id,data[Id],data[Last]) // last
=XLOOKUP(id,data[Id],data[Email]) // email

Both of these options above would work well with XLOOKUP. However, in the example shown, we only have the named range data (B5:E104). Is there a way to use the entire table directly with XLOOKUP? Yes. It’s a little tricky, but we can use the CHOOSECOLS function.

XLOOKUP with CHOOSECOLS

One way to use XLOOKUP with the named range data is to use the CHOOSECOLS function like this:

=XLOOKUP(id,CHOOSECOLS(data,1),CHOOSECOLS(data,2)) // first
=XLOOKUP(id,CHOOSECOLS(data,1),CHOOSECOLS(data,3)) // last
=XLOOKUP(id,CHOOSECOLS(data,1),CHOOSECOLS(data,4)) // email

The CHOOSECOLS function returns one or more columns from a range by numeric index. In the formulas above, we are using CHOOSECOLS to get the first column (1) for the lookup_array in all formulas. Then, for result_array , we vary the number as needed to get to provide the correct range for first name (2), last name (3), and email address (4).

Note: Excel purists will point out that we could also use the INDEX function to retrieve columns for XLOOKUP. Yes, absolutely.

XLOOKUP with DROP and TAKE

Yet another way to solve this problem is to use the TAKE function with the DROP function with the entire named range data:

=XLOOKUP(id,TAKE(data,,1),DROP(data,,1))

This formula will return all first, last, and email all in one step and the result will spill into the worksheet in three cells horizontally . To get all three values in a vertical array, you can wrap the above formula in the TRANSPOSE function :

=TRANSPOSE(XLOOKUP(id,TAKE(data,,1),DROP(data,,1)))

TRANSPOSE simply flips the orientation of the array returned by XLOOKUP.

Explanation

The general goal is to search through a cell for one of several specified values and return the first match found if one exists. The worksheet includes a list of colors in the range E5:E11 (which is named list ) and a series of short sentences in the range B5:B16. The task is to add a formula in column C that will search through each sentence in B5:B16 and extract the first color in E5:E11 that is found in each sentence. If no matching colors are found, the formula should return the value “na”. One way to solve this problem is with a formula that utilizes the ISNUMBER, SEARCH, and XLOOKUP functions. In older versions of Excel, you can use a formula based on INDEX and MATCH. Both methods are explained below.

The functions

Let’s first run through the functions used in the XLOOKUP formula:

  1. The ISNUMBER function checks if the given input or expression results in a number. It returns TRUE if the value is numeric, and FALSE if not.
  2. The SEARCH function is used to find the starting position of a specific substring within a string. The syntax is SEARCH(find_text, within_text, [start_num]). If the substring (find_text) is found, SEARCH returns the starting position as a number. If not, it results in an error.
  3. The XLOOKUP function is a modern upgrade to the older VLOOKUP function. XLOOKUP allows you to search for a key value in a specified range or array, and return a corresponding value from another range or array. The syntax is XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]).

The formula

Now, let’s look at the formula that appears in cell C5:

=XLOOKUP(TRUE,ISNUMBER(SEARCH(list,B5)),list,"na")

Here’s how the formula works step by step, working from the inside out:

SEARCH(list,B5)

The SEARCH function tries to find each color in the named range list (E5:E11) inside the sentence in cell B5. If a color is found, SEARCH returns the starting position as a number. If a color is not found, SEARCH returns an error. Because E5:E11 contains 7 colors, SEARCH returns 7 results in an array like this:

{#VALUE!;30;#VALUE!;#VALUE!;#VALUE!;#VALUE!;5}

Notice the second value is 30 and the seventh value is 5. These numbers indicate the numeric positions of the colors “white” and “blue” in cell B5. The #VALUE! errors indicate colors in E5:E11 that were not found. We need to convert these values into something more useful and for that, we use the ISNUMBER function:

ISNUMBER(SEARCH(list,B5))

This function is an error handler. Since the SEARCH function will return an error if the color is not found, ISNUMBER is used to turn the errors into FALSE (not a number) and the numbers (which indicate positions) into TRUE (is a number). The result from ISNUMBER is an array like this:

{FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE}

Note we have TRUE in the second and seventh positions, indicating a match on the colors White and Blue. Next, we have the XLOOKUP function, which is the main part of the formula

=XLOOKUP(TRUE,ISNUMBER(SEARCH(list,B5)),list,"na")

After ISNUMBER and SEARCH are evaluated, the array from ISNUMBER is returned to XLOOKUP as the lookup_array argument:

=XLOOKUP(TRUE,{FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE},list,"na")

XLOOKUP is configured to match the first TRUE in the array. When it finds a TRUE, it returns the corresponding color from the named range list (E5:E11). If no TRUE is found (i.e., no color from the list was found in the sentence), it returns “na”. This value can be omitted or customized as desired. To recap, this formula extracts the first color found from the list (E5:E11) in each cell from B5 to B16. It does so by searching for each color in the sentence, checking whether the result is a number (indicating a match was found), and then returning the corresponding color.

First match in sentence, or first match in list?

The language used in this example is ambiguous because it is not clear whether we are referring to the “first color found in the list of colors” or the “first color found in each sentence”. These are two distinctly different operations. The formula above returns the first match found in the color list. If multiple colors from the list appear in a sentence, the formula will return the color that appears first in the color list , not the color that occurs first in the sentence . If instead, you want to find the first matched color in a sentence (ignoring the order of colors in the list) we need to use a different formula like this:

=XLOOKUP(1,IFERROR(SEARCH(list,B5),0),list,"na",1)

This version of the formula has the same basic structure as the original formula. However, instead of using the ISNUMBER function as an error handler, it uses the IFERROR function . IFERROR is set to catch errors from SEARCH and remap them to a zero (0) value. After IFERROR runs, the lookup_array inside XLOOKUP looks like this:

=XLOOKUP(1,{0;34;0;0;0;0;5},list,"na",1)

XLOOKUP is configured to look for 1, and match_mode is set to 1, which means “exact match or next larger value”. In this case, XLOOKUP will match the last value (5), because it is the next larger value after 1, and return “white”, since “white” appears before “blue” in the sentence.

For more details on XLOOKUP, see How to use the XLOOKUP function .

INDEX and MATCH

In older versions of Excel that do not provide XLOOKUP, you can solve this problem with a formula based on INDEX and MATCH:

=INDEX(list,MATCH(TRUE,ISNUMBER(SEARCH(list,B5)),0))

Note: this is an array formula and must be entered with control + shift + enter in Excel 2019 and older.

This formula works in much the same way as the XLOOKUP version above. Working from the inside out, we use ISNUMBER and SEARCH to locate color matches in each sentence like this:

ISNUMBER(SEARCH(list,B5)

As explained above, SEARCH will return the positions of any colors that appear in each sentence, and ISNUMBER will convert results from SEARCH into TRUE and FALSE values. The result is delivered to the MATCH function as the lookup_array :

=INDEX(list,MATCH(TRUE,{FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE},0))

Notice MATCH is configured to look for TRUE, and there are 2 TRUE values in the lookup_array . The match_type argument is set to zero (0) to force an exact match. In this configuration, MATCH will match the first TRUE value and return 2 as a result directly to INDEX as the row_num argument:

=INDEX(list,2) // returns "blue"

INDEX will then return the second color in E5:E11 (“blue”) as a final result.

First match in sentence

As above, we need a different INDEX and MATCH formula to extract the first color that appears in a sentence, as opposed to the first color matched in list (E5:E11):

=INDEX(list,MATCH(AGGREGATE(15,6,SEARCH(list,B5),1),SEARCH(list,B5),0))

The main trick in this formula is the lookup_value inside the MATCH function, which is calculated with the AGGREGATE function like this:

AGGREGATE(15,6,SEARCH(list,B5),1) // get min value

Here, we use AGGREGATE to get the minimum value in the results returned by SEARCH. We need AGGREGATE because the array will contain errors (returned by SEARCH when colors aren’t found), and we need a function that will ignore those errors and still give us the minimum numeric value. AGGREGATE works well here because it has an option to ignore errors. The result from AGGREGATE is 5, which is returned to MATCH as the lookup_value . The lookup_array is created by the SEARCH function:

{#VALUE!;34;#VALUE!;#VALUE!;#VALUE!;#VALUE!;5}

We don’t use ISNUMBER in this case because we need to be able to find the number calculated by AGGREGATE. Back in the formula, we now have:

=INDEX(list,MATCH(5,{#VALUE!;34;#VALUE!;#VALUE!;#VALUE!;#VALUE!;5},0))

The result from MATCH is 7, because 5 is the seventh value in the lookup_array . MATCH returns this number to INDEX as the row_num argument:

=INDEX(list,7) // returns "white"

The final result is “white”, the first color found in the sentence.

For more details on INDEX with MATCH, see How to use the INDEX and MATCH .