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.
XLOOKUP is a 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:
This result is returned directly to the TRANSPOSE function:
To use XLOOKUP with multiple criteria, you can concatenate lookup values and lookup arrays directly in the formula. In the example shown, the formula in H8 is: = XLOOKUP ( H5 & H6 & H7 , B5:B14 & C5:C14 & D5:D14 , E5:E14 ) XLOOKUP...
To use XLOOKUP with multiple logical, build expressions with boolean logic and then look for the number 1. In the example XLOOKUP is used to lookup the first sale to Chicago over $250. The formula in G6 is: = XLOOKUP ( 1 ,( D5:D14 = "chicago...
XLOOKUP can be used to find values to the left of the lookup value. In the example shown, the formula in H6 is: = XLOOKUP ( H4 , E5:E14 , B5:B14 ) which returns 25, the height in column B for model H in row 12. XLOOKUP is a beta function , available...
The Excel XLOOKUP function performs lookups in vertical or horizontal ranges. It is a more robust and flexible successor to older functions like VLOOKUP, HLOOKUP, and LOOKUP. XLOOKUP supports approximate and exact matching, and wildcards (* ?)...
The Excel TRANSPOSE function "flips" the orientation of a given range or array. TRANSPOSE converts a vertical range to a horizontal range, or a horizontal range to a vertical range. You must enter the TRANSPOSE function as an array formula.
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.