Use 2 to do a simple wildcard match where ? means match any character and * means match any run of characters. Use 1 or -1 to allow a match against the nearest smaller (or larger) item when there is no exact match. Match_mode allows you to set the type of match you’d like to perform. If_not_found allows you to specify what should be returned if no match is found To perform advanced lookups, you can use XLOOKUP’s optional 4th and 5th mode arguments: match_mode and search_mode.
XLOOKUP(lookup_value,lookup_array,return_array) Let’s consider its signature in the simplest form: In its simplest form, XLOOKUP needs just 3 arguments to perform the most common exact lookup (one fewer than VLOOKUP). XLOOKUP is named for its ability to look both vertically and horizontally (yes it replaces HLOOKUP too!). In fact, it’s so fundamental to spreadsheeting that most users can recall the sense of achievement they felt when they first mastered VLOOKUP, myself included. For 35 years, VLOOKUP has been the first lookup function learned by Excel users and our 3rd most used function (after SUM and AVERAGE). VLOOKUP has been with Excel from the beginning it was included in Excel 1 for Macintosh released in 1985.
Before I describe XLOOKUP, I want to take a moment to reflect on VLOOKUP's incredible history and impact on spreadsheeting. We are excited to announce XLOOKUP, successor to the iconic VLOOKUP function. It will be available to users in Semi-Annual (Targeted) channel starting mid April 2020.
XLOOKUP is now available to all Office 365 Subscribers in the Monthly Channel and will be available to users in the Semi-Annual Channel starting in July.