Excel – XLOOKUP vs. VLOOKUP vs. INDEX / MATCH Functions

 

Excel – XLOOKUP vs. VLOOKUP vs. INDEX / MATCH Functions

On August 28, 2019, Microsoft announced the release of the XLOOKUP Function. The XLOOKUP Function is meant to replace both the VLOOKUP and HLOOKUP Functions. It will also replace the INDEX / MATCH function combo that was previously used to perform more powerful VLOOKUPs.

The XLOOKUP Function is being slowly released to Office 365 users (starting with Office 365 Insiders). So you might not see the new function available yet. Office 2019 users will not get XLOOKUP. So be careful using the XLOOKUP Function – make sure your end-users have access to the new function.

XLOOKUP Example

XLOOKUP Syntax

The XLOOKUP Syntax is:

XLOOKUP(lookup_value,lookup_array,return_array,[match_mode],[search_mode])

Where:

• lookup_value – What to look for
• lookup_array – Where to look
• return_array – What to output
• [match_mode] – (OPTIONAL) Specify type of match to perform. Default is Exact Match (see table below for all options)
• [search_mode] – (OPTIONAL) Specify type and direction of search. Default is First-To-Last (see table below for all options)

XLOOKUP Match_Mode

0 – Exact match will only find exact matches

1 (-1) – Will perform an exact match or find the next largest (smallest) item.

2 – Wildcard character match allows you to use ? or * wildcards for inexact matches.

XLOOKUP Search_Mode

1 – Search top to bottom (or left to right for horizontal lookup)

-1 – Search bottom to top (or right to left for horizontal lookup)

2 (-2) – Binary search on sorted data.  If you don’t know what a binary search is, you probably won’t ever need to perform one.

Why is XLOOKUP Better Than VLOOKUP?

1. The VLOOKUP Function requires that the lookup column be the left-most column in the data set. You can’t “lookup left”. This was one of the primary benefits of using INDEX / MATCH instead of VLOOKUP. However, the XLOOKUP does not have this limitation
2. XLOOKUP defaults to an exact match. VLOOKUP defaults to an “approximate” match, requiring that you add the “false” argument at the end of your VLOOKUP to perform an exact match. This was the cause of countless spreadsheet errors with users unintentionally performing approximate matches.
3. VLOOKUP formulas could not handle column insertions or deletions. If you inserted or deleted a column you would need to adjust the column index number in your VLOOKUP.  This is not a problem with the XLOOKUP Function.
4. XLOOKUP can perform horizontal or vertical lookups. The XLOOKUP replaces both the VLOOKUP and HLOOKUP.
5. XLOOKUP has smarter approximate matches. VLOOKUP data needed to be sorted smallest to largest. However XLOOKUP can perform searches in either direction.
6. XLOOKUP requires referencing fewer cells. VLOOKUP required you to input an entire data set, but XLOOKUP only requires you to reference the relevant columns or rows. By referencing fewer cells, the XLOOKUP will increase your spreadsheet calculation speed and potentially result in fewer circular reference errors.

 

XLOOKUP Examples in VBA

XLOOKUP is only available to the insider program of office 365 for now, so it is not yet publicly available. It will become available soon.

#evba #kingexcel #etipfree #eama