# XLOOKUP wildcard match example

=XLOOKUP(value,lookup,return,"not found",2)

To perform a partial match with XLOOKUP, supply 2 for the match mode argument to allow wildcards. In the example shown, the formula in H7 is:

which performs a wildcard match with the value in H4 and returns all 4 fields as the result. The TRANSPOSE function is optional and used here only to convert the result from XLOOKUP to a vertical array.

*beta function*, available only through the Office Insiders program.

### How this formula works

Working from the inside out, XLOOKUP is configured to find the value in H4 in the Last name column, and return all fields. In order to support wildcards, *match_mode* is provided as 2:

XLOOKUP(H4,D5:D15,B5:E15,2) // match Last, return all fields

- The
*lookup_value*comes from cell H4 - The
*lookup_array*is the range D5:D15, which contains Last names - The
*return_array*is B5:E15, which contains all all fields - The
*not_found*argument is set to "Not found" - The
*match_mode*is is 2, to allow wildcards - The
*search_mode*is not provided and defaults to 1 (first to last)

Since H4 contains "corr*", XLOOKUP finds the first Last name beginning with "corr" and returns all four fields in a horizontal array:

{648,"Sharyn","Corriveau","Support"}

This result is returned directly to the TRANSPOSE function:

=TRANSPOSE({648,"Sharyn","Corriveau","Support"})

The TRANSPOSE function changes the array from horizontal to vertical:

{648;"Sharyn";"Corriveau";"Support"} // vertical array

and the array values spill into the range H7:H10.

### With implicit wildcard

In the example above, the asterisk wildcard (*) is entered explicitly into the lookup value. To pass in the wildcard implicitly, you can adjust the formula like this:

Above, we concatenate the astrisk wildcard (*) to the value in H4 in the formula itself. This will append the asterisk to any value entered in H4, and XLOOKUP will perform a wildcard lookup.

## Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.