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.
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:
One of the nice advantages of XLOOKUP over VLOOKUP is that XLOOKUP can work with arrays directly, instead of requiring ranges on a worksheet. This makes it possible to assemble arrays in the formula, and push these into the function. Working one...
XLOOKUP can handle arrays natively, which makes it a very useful function when constructing criteria based on multiple logical expressions. In the example shown, we are looking for the order number of the first order to Chicago over $250. We are...
Whereas VLOOKUP is limited to lookups to the right of the lookup column, XLOOKUP can lookup values to the left natively. This means XLOOKUP can be used instead of INDEX and MATCH to find values to the left in a table or range. In the example shown,...
The Excel XLOOKUP function is a modern and flexible replacement for older functions like VLOOKUP, HLOOKUP, and LOOKUP. XLOOKUP supports approximate and exact matching, wildcards (* ?) for partial matches, and lookups in vertical or horizontal...
The Excel TRANSPOSE function "flips" the orientation of a given range or array: TRANSPOSE flips a vertical range to a horizontal range, and flips a horizontal range to a vertical range.
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.