This is so cool. Mind Blown.
XLOOKUP is conceptually similar to the other LOOKUP Functions, eXcept it doesn’t have the limitations of VLOOKUP and is easier to use!
- Use it in place of VLOOKUP or HLOOKUP.
- Default match is exact not approximate
- Match options are greater
- File_not_found options
Searches a range for a match and returns the corresponding item from a second range. By default, an exact match is used. If the second range includes more than one column of data, multiple values are returned.
Advantages of XLOOKUP
With VLOOKUP, the lookup_value had to be the first column in the table_array. XLOOKUP resolves that problem. The lookup_array shows Excel exactly which range has the value it’s looking up.
With VLOOKUP, adding, deleting or moving columns to your table_array meant you had to edit the col_index_num in the function. XLOOKUP resolves that problem too. The return_array shows Excel which range has the value to return.
Look at the image above. The lookup_value can be in whatever column
(M14:M18) rather than the first column. Instead of the col_index_num (as VLOOKUP does), you use the full range reference (L14:L18).
XLOOKUP searches for an exact match automatically. You can use the if_not_found argument to display a message (instead of an error) if the lookup value isn’t found.
More advantages with return_array
By expanding the return_array to include multiple columns*, the result automatically expands to include the return for the adjoining columns.
* When using multiple columns in the return_array, the lookup_array shouldn’t be included within the return_array range.
Arguments defined
- Lookup_value: the value searched for
- Lookup_array: the array or range to search
- Return_array: the array or range to return
- If_not_found (default returns error if no match):
- 0 displays a zero if there is no match
- “text” displays whatever you put in the quotes
- Match_mode (default exact):
- 0 matches exact
- -1 matches exact or next smaller
- 1 matches exact or next larger
- 2 matches wildcard
- Search_mode (default first to last):
- 1 searches first to last
- -1 searches last to first
- 2 searches binary sorted ascending
- -2 searches binary sorted descending
Call me. Let’s play with this together and learn how cool it is.
As always, we’re available to help you with your individual questions. With over twenty years of experience, we are proud to have clients in many industries including: Real Estate, construction, manufacturing, non-profits, and research and development.
You can reach us by calling or texting 775 . 525 . 0885 or emailing info [at] MyRenoComputerTutor [dot] com
We’re dedicated to helping people understand what they’re doing. So please reach out. You can contact us via email, or voicemail, and you can even text us to get your training scheduled. Diana also trains for Washoe County and UNR Extended Studies.
