digest
Short Summary
Main recommendation: Use the XLOOKUP function (Excel's new lookup function, available in Office 365 Insider as of September 13, 2019) to look up an ID code using multiple lookup values, such as product, sales rep, and state, by nesting XLOOKUP inside another XLOOKUP and joining the criteria with the ampersand (&) operator. The video contrasts this new method with the older INDEX/MATCH approach, which required more complicated keystrokes.
Key Points
- The state determines the column, while the product and sales rep together determine the row, so the lookup needs two layers.
- The inner XLOOKUP looks up the state across the column headers and returns the full matching column, since the return array has three columns and multiple rows.
- In the new Excel calculation engine, a formula that returns multiple items spills its answer, and that spilled column becomes the return range for the outer XLOOKUP.
- Two lookup values are combined with the ampersand (&) join operator, and the two matching columns are joined the same way, so the outer XLOOKUP can handle the array operation directly.
- The older method used INDEX with a two-way MATCH; a join array operation inside INDEX would have required Control+Shift+Enter, but leaving the row number empty avoided this.
Details Worth Noting
- Concrete instructions:
- Enter the inner XLOOKUP with the state as the lookup value, the column headers as the lookup array, and the full data block (three columns) as the return array, which returns the whole column for that state.
- Click in the lookup cell and press F2, so the delivered spilled column becomes the return range in the second XLOOKUP.
- Start the outer formula with =XLOOKUP(, then set the lookup value to product & sales rep (product joined to sales rep with &).
- Set the lookup array to the product column joined to the sales rep column with &, then add a comma and the return range (the spilled column from the inner XLOOKUP).
- Close the parentheses and press Enter; no Control+Shift+Enter is needed because the array operation works inside XLOOKUP.
- To test, change the state to Washington and confirm the returned value (the example shows quad sales rising by one).
- The presenter says XLOOKUP is only in Office 365 Insider as of September 13, 2019, and that Microsoft expected it to reach all Office 365 users within a few months.
- The older approach used INDEX with two MATCH functions: one MATCH on the joined product and sales rep values for the row number, and one MATCH on the state for the column number, with an empty row argument to return the full column.
- The presenter mentions a comprehensive XLOOKUP video and a next video that uses XLOOKUP with three lookup values for lookup adding.
Bottom Line
XLOOKUP simplifies multi-criteria lookups in newer Excel versions by letting you combine criteria with & and nest one XLOOKUP inside another, avoiding the older INDEX/MATCH workaround and array-key shortcuts.
