ExcelAt40 – Day 11: Lookup Wars – VLOOKUP vs XLOOKUP

Below the mainly highlight:

VLOOKUP
•     Looks up values vertically, always searching the leftmost column of your range.
•     Requires you to provide a column index number to return a value from.
•     Can only search from left to right; it cannot look to the left.
•     Needs to be wrapped inside an IFERROR function to handle errors cleanly.
•     Is prone to breaking if columns are added or rearranged because of the fixed index.
XLOOKUP
•     Can look both vertically and horizontally, more flexible.
•     Instead of an index, you directly specify the return range.
•     Has a built-in argument to specify what to return if the lookup value isn’t found, so no need for IFERROR.
•     More robust against changes in your data layout because it refers to ranges directly.
•     Supports exact, approximate, and wildcard matches natively.