Alternatives to VLOOKUP In Excel

Vlookup Alternatives

The VLOOKUP fetches a value from a column based on the arguments supplied to it. However, the VLOOKUP has a number of limitations. This article discusses the different alternatives that can be used in its place. For example, the combination of the INDEX and MATCH functions is a powerful alternative to the VLOOKUP function.

To choose an appropriate alternative, one must know which flaws of the VLOOKUP are eliminated by it. So, let us begin by going through the limitations of the VLOOKUP, followed by an explanation of its alternatives. After each alternative, the reasons why it may be preferred over the VLOOKUP have been listed.

Limitations of the VLOOKUP Function of Excel

The limitations of the VLOOKUP function are listed as follows:

• It can search only in the leftmost column of the table array. As a result, it requires the lookup column to be on the left of the return column.
• It does not update on its own when new columns are added or deleted from the table array. Such additions or deletions require the column index number to be changed manually in the VLOOKUP formula.
• It can accept a maximum of 255 characters as the lookup value. If the lookup value exceeds this length, the function returns an error.
• It takes time to process when there are multiple VLOOKUP formulas and the datasets are too large. In such cases, the performance of Excel may slow down.
• It may return errors when the VLOOKUP formula is dragged and copied to the other cells of the worksheet. To prevent errors on copying, one may need to rewrite several VLOOKUP formulas.

Alternatives to the VLOOKUP Function of Excel

There are a number of alternatives to the VLOOKUP function. However, this article discusses the following substitutes (with examples) in Excel:

1. INDEX MATCH combination
2. LOOKUP function
3. XLOOKUP function
4. FILTER function

#1–INDEX MATCH Combination

Let us first understand the syntax and the arguments of the INDEX and MATCH functions.

INDEX function of Excel

The INDEX function fetches the value of a cell based on the specified row and column numbers. The array to which this cell belongs is also supplied to the INDEX function.

The syntax of the INDEX function is shown in the following image:

The INDEX function accepts the following arguments in the array form:

• Array: This is the range of cells from which the value is to be fetched.
• Row_num: This is the row number of the array from which a value is to be returned.
• Column_num: This is the column number of the array from which a value is to be returned.

The “array” and “row_num” are mandatory arguments, while “column_num” is an optional argument.

Note: If the “array” is a single row, the “column_num” argument must be specified to fetch a value though the “row_num” can be omitted. Likewise, if the “array” is a single column, the “row_num” must be specified to fetch a value though the “column_num” can be omitted.

If the “array” consists of multiple rows and columns, both the “row_num” and the “column_num” arguments must be specified to fetch a value.

MATCH function of Excel

The MATCH function looks for a value and returns its relative position in the supplied range.

The syntax of the MATCH function is shown in the following image:

The MATCH function accepts the following arguments:

• Lookup_value: This is the value whose position is required. It can be a number, cell reference, logical value (true and false) or a text string.
• Lookup_array: This is the range of cells within which the search should be conducted.
• Match_type: This can take any of the following numbers:
  • 1 (approximate match) = It looks for the largest value in the “lookup_array” that is smaller than or equal to the lookup value. With this match type, the “lookup_array” must be arranged in ascending order.
  • 0 (exact match) = It looks for the first value in the “lookup_array” that is exactly equal to the lookup value. With this match type, the data need not be arranged or sorted.
  • -1 (approximate match) = It looks for the smallest value in the “lookup_array” that is greater than or equal to the lookup value. With this match type, the “lookup_array” must be arranged in descending order.

The “lookup_value” and “lookup_array” are mandatory arguments, while “match_type” is optional. If the “match_type” is omitted, the MATCH function assumes it as 1.

However, when the INDEX MATCH functions are used as an alternative to the VLOOKUP, the match type is usually set at 0. This is because an exact match is required in most cases.

Example

There are three images titled “Image 1,” “Image 2,” and “Image 3.” The following information is given:

Image 1

Image 2

Image 3

• “Image 1” shows the IDs (column G), names (column H), and revenues generated in different months (columns A to F) by certain products of an organization.
• “Image 2” shows a blank cell K2 which will contain the revenue of ID “P1078” for April.
• “Image 3” suggests that the lookup column (product ID or column G) is to the right of the return column (April or column D). So, one cannot look for a certain product ID and fetch the corresponding revenue (from columns A to F) by using the VLOOKUP function of Excel.

We want to perform the following tasks in Excel:

• Supply only the row number to the INDEX function to fetch the revenue of “P1078” for April (in cell K2).
• Supply both row and column numbers to the INDEX function by nesting two MATCH functions within it. This should also fetch the revenue of “P1078” for April in cell K2. Explain the INDEX MATCH formula thus used.
• Fetch the revenue of “P1252” for February by using the INDEX and MATCH functions.

The steps to perform the given tasks are listed as follows:

Step 1: Open the INDEX function in cell K2.

Step 2: Enter the first argument as D2:D10. This is the array from which the sales of “P1078” must be fetched. Notice that this array consists of a single column (column D).

Step 3: Specify the “row_num” argument as 6. The sixth row of the array D2:D10 is row 7 of the dataset. The value needs to be fetched from this row.

Step 4: Close the parenthesis as the “column_num” argument is not required. Press the “Enter” key.

The output is shown in the following image. So, the INDEX function has fetched the revenue of “P1078” for April, which is $6,251.

Note: Remember that when the array is a single column, the “row_num” of the INDEX function is sufficient to fetch a value.

Step 5: Let us supply both row and column numbers to the INDEX function by nesting two MATCH functions within it. For this, open the INDEX function and select the complete dataset (A2:F10) as the “array” argument.

Note: When the MATCH is nested within the INDEX function, the output of the former becomes the input of the latter. So, each time the “lookup_value” of the MATCH function changes, its output changes, causing a change in the row and column numbers of the INDEX function. Such row and column numbers are said to be dynamic.

Step 6: Open the first MATCH function.

Step 7: Supply the reference J2 to the MATCH function. So, the ID “P1078” is the “lookup_value.” Notice that this is the vlookup value implying that it needs to be looked up vertically in a column.

Step 8: Supply the “lookup_array” as G2:G10. The vlookup value “P1078” needs to be searched in column G. This is because column G contains the list of product IDs.

Step 9: Set the “match_type” as 0. This is because we want an exact match for the vlookup value “P1078.” Next, close the parenthesis as all arguments of the first nested MATCH have been entered.

The formula “MATCH(J2,G2:G10,0)” will return the row number for the vlookup value “P1078” from the range G2:G10.

Step 10: Open the second MATCH function.

Step 11: Enter the reference K1 as the “lookup_value” of the second MATCH function. Notice that “April” is the hlookup value as this needs to be searched horizontally in a row.

Step 12: Specify the “lookup_array” as A1:F1. So, the hlookup value “April” needs to be looked up in row 1 of the dataset. This is because this row contains the months in the stated range (A1:F1).

Step 13: Enter the “match_type” of the second MATCH function also as 0. This is because an exact match of the value “April” is required.

The formula “MATCH(K1,A1:F1,0)” will return the column number of “April” from the range A1:F1.

Step 14: Press the “Enter” key as the complete INDEX MATCH formula has been entered. The output in cell K2 is $6,251. This is shown in the following image.

Explanation of the INDEX MATCH formula: Let us break the preceding INDEX MATCH formula into parts to understand it. These parts are explained as follows:

• The first MATCH function [MATCH(J2,G2:G10,0)] returns 6, which becomes the “row_num” of the INDEX function. This is because the vlookup value “P1078” is in the sixth row of the range G2:G10.
• The second MATCH function [MATCH(K1,A1:F1,0)] returns 4, which becomes the “column_num” of the INDEX function. This is because the hlookup value “April” is in the fourth column of the range A1:F1.
• The INDEX function processes the formula “INDEX(A2:F10,6,4).” This returns the value at the intersection of the sixth row and the fourth column of the array A2:F10. The value at this location is $6,251.

Note: When both row and column numbers are supplied to the INDEX function, a value is fetched from the intersection of the row and column of a dataset. This is called a two-way lookup. The MATCH function is used with INDEX to supply dynamic row and column numbers to the latter.

Step 15: Change the product ID and month in cells J2 and K1 respectively. We have entered “P1252” and February in these cells. Notice the change in output in cell K2 of the following image.

Hence, the INDEX MATCH function has fetched the revenue of “P1252” for February.

The INDEX MATCH may be preferred over the VLOOKUP for the following reasons:

• The INDEX MATCH is flexible as it can look from right to left as well as from left to right. In other words, it does not care about the location of the lookup and return columns.
• The INDEX MATCH updates with any insertions or deletions of the row and/or column of the array. The reason is that ranges are supplied as the “array” and the “lookup_array,” unlike the column index number of the VLOOKUP function.
• The INDEX MATCH can return a value from the specified row when a single vertical “array” and a dynamic “column_num” are supplied to the INDEX function. Likewise, the INDEX MATCH can also return a value from the specified column and the intersection of a row and column. However, the VLOOKUP can return a value from a column only.
• The INDEX MATCH does not bother about the size of the lookup value, unlike the VLOOKUP function.
• The INDEX MATCH works faster than the VLOOKUP formulas.

#2–LOOKUP Function

The LOOKUP function looks for a value in a single row or column and returns a corresponding match from the same position of a different row or column. The LOOKUP function has two forms, vector and array.

This article discusses the vector form of the LOOKUP as an alternative to the VLOOKUP.

The syntax of the LOOKUP function is shown in the following image:

The LOOKUP function accepts the following arguments in the vector form:

• Lookup_value: This is the value to be searched. It can be numeric, textual, logical (true and false), or a cell reference.
• Lookup_vector: This is the range of a single row or column in which the “lookup_value” is to be searched. The values of this range must be sorted in ascending order.
• Result_vector: This is the range of a single row or column from which the output is to be returned.

The “lookup_value” and “lookup_vector” are mandatory arguments, while the “result_vector” is optional. If the “result_vector” is omitted, the output is returned from the “lookup_vector.”

Note 1: Both the “lookup_vector” and the “result_vector” should be of the same size. The LOOKUP function looks for an exact match first and, if not found, returns an approximate match.

Note 2: In the latest versions of Excel, the LOOKUP function has been kept only for backward compatibility. So, it may or may not work as desired.

Example

Working on the dataset of Example #1, we want to fetch the revenue of product “P1099” for May. The match (or output) should be displayed in cell K2. Use the vector form of the LOOKUP function of Excel.

The steps to perform the given task by using the LOOKUP function are listed as follows:

Step 1: Arrange the values of the product ID column in ascending order. This is because column G is the “lookup_vector.” For sorting, select the entire range A1:H10.

Step 2: Press the keys “Alt+D+S” one by one. The “sort” window opens, as shown in the following image.

Step 3: In the “sort” window, perform the following actions:

• In the “sort by” drop-down (under “column”), select “product ID.”
• In the “sort on” drop-down, let “cell values” remain selected.
• In the “order” drop-down, select “A to Z.”
• Click “OK” in the “sort” window.

The preceding selections will sort the product ID column from the smallest to the largest value.

Step 4: The product ID column (column G) is sorted in ascending order. The values of all the other cells of the dataset adjust according to the new arrangement.

Step 5: Open the LOOKUP function in cell K2.

Step 6: Enter the reference J2 as the “lookup_value” argument. This cell contains the lookup value “P1099.”

Step 7: Select the range G2:G10 as the “lookup_vector.” The value “P1099” needs to be looked up in this range. Notice that this is a one-column range consisting of 9 rows.

Step 8: Select the range E2:E10 as the “result_vector.” The match needs to be returned from this range. Notice that this range is also a one-column range consisting of 9 rows.

Therefore, our “lookup_vector” and the “result_vector” are of the same size.

Step 9: Close the parenthesis of the LOOKUP function. Next, press the “Enter” key. The output is shown in cell K2 of the following image.

So, the LOOKUP has fetched the revenue of “P1099” for May. This is $7,031.

The LOOKUP function may be preferred over the VLOOKUP for the following reasons:

• The LOOKUP function can perform both horizontal and vertical lookups, unlike the VLOOKUP, which is limited only to vertical lookups.
• The LOOKUP does not care about the placement of the lookup and return columns, unlike the VLOOKUP function, which can look only from left to right.
• The LOOKUP formulas are easy to use and edit than the VLOOKUP formulas.

#3–XLOOKUP Function

The XLOOKUP function looks for a value in one column and returns a corresponding match from the same row of another column. This function can look in any direction and can also return an array of related matches.

The XLOOKUP function is available in Excel for Microsoft 365, Excel for the web, and Excel 2021.

The syntax of the XLOOKUP function is shown in the following image:

The XLOOKUP function accepts the following arguments:

• Lookup_value: This is the value to be searched.
• Lookup_array: This is the range or array where the lookup value is to be searched.
• Return_array: This is the range or array from which a corresponding match is required.
• If_not_found: This value will be returned in case a match is not found. If this is not supplied, the “#N/A” error is returned.
• Match_mode: This decides the kind of match to be performed. It can take any of the following values:
  • 0 – It returns an exact match. However, if an exact match is not found, it returns the “#N/A” error.
  • -1 – It returns an exact match. However, if an exact match is not found, it returns the next smaller value.
  • 1 – It returns an exact match. However, if an exact match is not found, it returns the next larger value.
  • 2 – It returns a partial match by using wildcard characters [like an asterisk (*) and a question mark (?)].
• Search_mode: This decides the direction of search for the lookup value. It can take any of the following values:
  • 1 – It searches from top to bottom in the “lookup_array.”
  • -1 – It searches from bottom to top in the “lookup_array.” This is helpful if the last match of the lookup value is needed.
  • 2 – It helps perform a binary search. To get the right results, the data needs to be sorted in ascending order.
  • -2 – It helps perform a binary search. To get the right results, the data needs to be sorted in descending order.

The arguments “lookup_value,” “lookup_array,” and “return_array” are mandatory, while the remaining are optional. If “match_mode” and “search_mode” are omitted, XLOOKUP assumes them as 0 and 1 respectively.

Example

Working on the dataset of example #1, we want to perform the following tasks in Excel:

• Fetch the revenue of “P1856” for April in cell K2. Use the XLOOKUP function.
• Perform a two-way lookup by nesting one XLOOKUP within the other. This should also fetch the revenue of “P1856” for April. Explain the nested XLOOUP formula thus used.
• Fetch the value of “P1966” for February by using the nested XLOOKUP function.

The steps to perform the given tasks by using the XLOOKUP function are listed as follows:

Step 1: Open the XLOOKUP function in cell K2.

Step 2: Select cell J2 as the first argument. This cell contains the lookup value “P1856.”

Step 3: Select the range G2:G10. This is the “lookup_array” of the XLOOKUP function. So, the value “P1856” will be searched in column G.

Step 4: Select the range D2:D10. This is the “return_array” of the XLOOKUP function. So, a related match will be returned from column D.

Step 5: Close the parenthesis and press the “Enter” key as the main arguments have been entered. The output is $6,183 in cell K2 of the following image.

Notice that the output of the formula “=XLOOKUP(J2,G2:G10,D2:D10)” will not change with a change in the product ID (cell J2) or the month (cell K1). In order to obtain an output that updates with the change, a two-way lookup needs to be performed.

Step 6: To perform a two-way lookup, let us nest one XLOOKUP within the other. For this, open the XLOOKUP and enter the arguments K1 and A1:F1.

Cell K1 contains the first “lookup_value” that will be searched horizontally in the range A1:F1. So, “April” is the hlookup value.

Note: A two-way lookup helps fetch a value at the intersection of a row and column.

Step 7: Open the second XLOOKUP function.

Step 8: Supply the reference J2 as the second “lookup_value.”

Step 9: Select G2:G10 as the second “lookup_array.” So, the ID “P1856” will be searched in column G. Therefore, “P1856” is the vlookup value.

Step 10: Select A2:F10 as the “return_array.” So, the output will be returned from the range A2:F10. This range contains the revenues of all the products for six months.

Step 11: Close the parenthesis and press the “Enter” key. The double XLOOKUP returns $6,183 as the output.

Explanation of the nested XLOOKUP formula: The two XLOOKUP functions search for the values “April” (hlookup value) and “P1856” (vlookup value) in the ranges A1:F1 and G2:G10 respectively. Then, the nested XLOOKUP formula returns a match that is at the intersection of the two lookup values. This match is $6,183.

Hence, with a nested XLOOKUP, one can perform a horizontal and a vertical lookup together. Moreover, any changes in the supplied lookup values cause a change in the output.

Step 12: Change the month (in cell K1) to February and the product ID (in cell J2) to “P1966.” Press the “Enter” key and notice the updated output in cell K2. This is $5,044.

The XLOOKUP may be preferred over the VLOOKUP for the following reasons:

• The XLOOKUP can look in any direction (up, down, right or left) both vertically and horizontally, unlike the VLOOKUP, which can look only in the leftmost column of an array.
• The XLOOKUP can fetch the last occurrence of the lookup value by setting the “search_mode” as -1. The VLOOKUP can fetch only the first occurrence of the lookup value.
• The XLOOKUP formula updates with any insertions or deletions to the dataset, unlike the VLOOKUP formula, which does not update in such situations.
• The XLOOKUP can fetch a list of values related to the lookup value, unlike the regular VLOOKUP, which fetches a single related match.
• The XLOOKUP can return a customized message in case a match is not found.

#4–FILTER Function

The FILTER function returns a single value or an array of values based on the specified criteria (one or more conditions). The array of values is spilled in a range beginning from the cell in which the formula is entered. The FILTER function is available in Excel for Microsoft 365.

The syntax of the FILTER function is shown in the following image:

The FILTER function accepts the following arguments:

• Array: This is the entire range or array of cells from which certain values need to be fetched. In other words, this range will be filtered.
• Include: This is the criteria (one or more conditions) that must return only the Boolean values (true and false). The condition is applied to a range. The number of rows and columns of the criteria range must be the same as that of the “array” argument. For instance, “A2:A10=5” is a criterion or condition.
• If_empty: This value will be returned if none of the entries of the “array” argument meet the criteria. In other words, if there are no results to display, the value of this argument is returned.

The arguments “array” and “include” are mandatory, while “if_empty” is optional.

Example

Working on the dataset of example #1, fetch the revenue of “P1372” for January. The output should display in cell K2. Use the FILTER function of Excel and explain the formula thus used.

The steps to fetch a single value by using the FILTER function are listed as follows:

Step 1: Open the FILTER function in cell K2.

Step 2: Select the array A2:A10 as the only value that needs to be fetched belongs to this range.

Step 3: Begin the “include” argument by selecting the range G2:G10. The criterion will be applied to this range.

Step 4: Type the criterion as “G2:G10=J2.” So, the FILTER function will fetch the value that satisfies this criterion.

Step 5: Close the parenthesis and press the “Enter” key. The output is $7,234 in cell K2 of the following image.

Explanation of the FILTER formula: Excel checks which value of the range G2:G10 is equal to J2 (or P1372). Only cell G10 matches the value of cell J2. So, the FILTER function fetches the value from row 10 of the “array” A2:A10.

The result would have been different had we supplied the entire range A2:H10 as the “array” argument of the FILTER function.

The FILTER function may be preferred over the VLOOKUP for the following reasons:

• The FILTER function can return both a single value and an array of values (horizontal and vertical), unlike the VLOOKUP, which can return only a single value.
• The FILTER function can work with one or more conditions (criteria). The usual VLOOKUP formula does not work with conditions.
• The array of values returned by the FILTER function automatically updates with a change in the values of the source dataset.

Frequently Asked Questions

Download Template

This article must be helpful to understand the Alternatives to VLOOKUP in Excel, with its formula and examples. You can download the template here to use it instantly.

Recommended Articles

This has been a guide to the Alternatives to VLOOKUP In Excel. Here we learn the top 4 alternatives to the VLOOKUP function including INDEX MATCH combination, LOOKUP function, XLOOKUP function and Filter function with examples and downloadable Excel template. You may also look at these useful functions –

• HLOOKUP
• TRANSPOSE In Excel
• FLASHFILL in Excel