According to Microsoft, VLOOKUP is the third most used worksheet function, behind the SUM and AVERAGE functions, respectively. VLOOKUP is used to look up information in a spreadsheet based on criteria that you provide. VLOOKUP has four arguments:
lookup_value – what to look for, in the case of Figure 1 below the value in cell A3
table_array – the cell coordinates of a list or a table name that typically will contain 2 or more columns
col_index_num – the column from which to return data
range_lookup – type of match, use 0 or FALSE for an exact match, or 1 or TRUE for an approximate match
The benefit of lookup functions is that if you were to say change the value of cell A2 to 40300 then VLOOKUP would return the corresponding amount. This eliminates the need to rewrite formulas or copy and paste data again when needs change within a spreadsheet. VLOOKUP is powerful, but the col_index_num argument makes for some brittle formulas. If you add or delete a column in the table_array you must manually update the col_index_num or else encounter a #REF! error or return data from the wrong column. XLOOKUP resolves this by eliminating the col_index_num argument.
Figure 1: VLOOKUP vs. XLOOKUP
The biggest catch with XLOOKUP is that you can only use it with Microsoft 365, Excel 2021, or Excel Online. The function returns #NAME? in Excel 2019 and earlier. XLOOKUP actually has 6 arguments, but for this article I’ll just explain the first three:
lookup_value – this works exactly the same as in VLOOKUP.
lookup_array – The row or column that has the data you’re looking for. VLOOKUP can only look up and down columns, you must use HLOOKUP to look across rows. XLOOKUP can look both ways.
return_array – The row or column that has the data you wish to return.
Both VLOOKUP and XLOOKUP return #N/A if a match cannot be found. The fourth argument for XLOOKUP allows you to specify an alternate value to display instead of #N/A. Notice that XLOOKUP doesn’t require you to indicate if you want an exact or approximate match. This is because unlike VLOOKUP, which defaults to an approximate match, XLOOKUP defaults to an exact match. Use the fifth argument to specify otherwise. Finally, the sixth argument allows you to specify whether XLOOKUP should search from top to bottom or from bottom to top. That is functionality that is new to Excel, there hasn’t been an easy way to do that previously without sorting your list.