What are Alternatives to VLOOKUP in Google Sheets
VLOOKUP in Google Sheets is used to search for related information by row. It has users because it can be used to look up data in a table based on criteria that we set. Here, V represents vertical, where it will search vertically in the first column. However, VLOOKUP has several limitations which may not give you the desired result in many scenarios. While VLOOKUP is good in most cases, it may not work properly when you use it in large and complex sheets.
So, the best alternatives to VLOOKUP are a number of options, including INDEX MATCH, XLOOKUP, and HLOOKUP. The INDEX MATCH combination is very flexible, which makes it superior to VLOOKUP in many ways. Similarly, XLOOKUP is a more versatile function than VLOOKUP that can handle various criteria in addition to VLOOKUP. The XLOOKUP handles exact matches by default. HLOOKUP is usually used to sort data horizontally in a table. For example, we use XLOOKUP to find the marks of a student, Andy, as shown below.

List of Alternatives to VLOOKUP in Google Sheets
Let us look at the list of alternatives we have to VLOOKUP in Google Sheets.
INDEX & MATCH
Here, we demonstrate an alternative way to use VLOOKUP in Google Sheets: a combination of INDEX and MATCH functions. Let’s examine their syntax and uses.
INDEX function
The INDEX function in Google Sheets returns a value from an array based on the row and column numbers specified. The syntax of the INDEX function is as follows:
=INDEX(array, row_num, [column_num])
- array – the range of cells from which you want to return a value.
- row_num – the row number from which you want to return a value.
- column_num – the column number in the array from which you want to return a value.
MATCH function
The MATCH function searches for a lookup value in a range of cells and returns the relative position of a value in the range.
The syntax of the MATCH function is as follows:
=MATCH(lookup_value, lookup_array, [match_type])
- lookup_value – the value you are looking for.
- lookup_array – the range of cells being searched.
- match_type – specifies whether to return an exact match or the nearest match:
- 1 approximate match
- 0 – exact match
- -1 – approximate match
We have a dataset where we must extract the quantity of an item using the INDEX-MATCH function.
Step 1: Below is the table. We use the INDEX-MATCH function as follows.

Step 2: Apply the following function in cell C3.
=INDEX(B1:B7, MATCH(C2,A1:A7,0))
- The MATCH function searches for the lookup value in C2, “Oranges,” in the range A1:A7 and returns 5 because “Oranges” is fifth in the lookup array.
- The row number goes directly to the row_num argument of INDEX instructing it to return a value from that row.

Step 3: Press Enter. The value equivalent to the index number is retrieved.

HLOOKUP function
HLOOKUP syntax
=HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)
- lookup_value – The value to search for.
- table_array -The table from which to retrieve data.
- row_index_num – The row number from which to retrieve data.
- range_lookup -[optional] A boolean to indicate an exact match or approximate match.
Let us implement the same example and see how to use HLOOKUP for it. We will look up the quantity of apples using HLOOKUP.

Step 1: Type the following formula in D3
=HLOOKUP(B4, A1:G2, 2, FALSE)
Step 2: You get the following result.

XLOOKUP function
The XLOOKUP function returns the values in the result range based on the position where a match was found in the lookup range. If no match is found, it returns the closest match.
Syntax
=XLOOKUP(search_key, lookup_range, result_range, missing_value, match_mode, search_mode)
- search_key: The value to search for. For example, 42, “Cats”, or B24.
- lookup_range: The range to consider for the search. This range must be a singular row or column.
- result_range: The range to consider for the result. This range’s row or column size should be the same as the lookup_range, depending on how the lookup is done.
- missing_value: [OPTIONAL – #N/A by default] The value to return if no match is found.
- match_mode: [OPTIONAL – 0 by default] The manner in which to find a match for the search_key.
- 0 is for an exact match.
- 1 is for an exact match or the next value that is greater than the search_key.
- -1 is for an exact match or the next value that is lesser than the search_key.
- 2 is for a wildcard match.
search_mode: [OPTIONAL – 1 by default] The manner in which to search through the lookup_range.
Step 1: Let us look at how to retrieve a value using XLOOKUP. Consider the table below.

=XLOOKUP(“Butter”, A2:A, B2:B)
Step 2: You get the following result.

Examples
Let us look at some simple examples below.
Example #1 – INDEX & Match
Let us try to understand how to use the INDEX-MATCH function as an alternative for VLOOKUP in Google Sheets.
We have a list of some countries and their cities with the maximum number of millionaires.

Step 1: To find the millionaire population of a certain city, let us use the following INDEX MATCH Google Sheets formula:=INDEX(B2:C10, MATCH(“Japan”, A2:A8, 0))

Step 2: Press Enter. You get the equivalent details.

Step 2: Press Enter. You get the equivalent details.
- The MATCH function searches for the lookup value “Japan” in A2:A6, returns the number 2, as “Japan” is second in the lookup array. This row number goes
- The row number goes directly to the row_num argument of INDEX. Hence, it returns the values from that row.
Example #2 – HLOOKUP
Let us look at another example of an alternative to VLOOKUP in Google Sheets: we use HLOOKUP as an alternative to VLOOKUP in Google Sheets.

Step 1: To find the sales for April, we can use the HLOOKUP function instead of VLOOKUP.
Step 2: Enter the following formula in cell A4.
=HLOOKUP(“Apr”, A1:E2, 2, FALSE)

Here, thelookup value in “Apr.”
- A1: E2 is the range of the table.
- 2 is the row number to retrieve data from.
- FALSE means an exact match.
Example #3 – XLOOKUP
Let us look at an example where we use XLOOKUP as an alternative to VLOOKUP in Google Sheets. Look at the below table.

Step 1: Let us look at the formula used to find the price of apples. Use the following formula.
=XLOOKUP(“Apples”, A2:A, B2:B)

Step 2: Press Enter. You get the price of apples.

The formula is used as a replacement for the VLOOKUP.
=VLOOKUP(“Apples”, A2:B7, 2, FALSE), which yields the same result.
Important Things to Note
- XLOOKUP combines the capabilities of VLOOKUP and HLOOKUP, making it easier to use. It also has a default option for exact matches as an alternative to VLOOKUP in Google Sheets.
- VLOOKUP mainly works well with smaller datasets where performance is not a concern.
- VLOOKUP requires the lookup column to be the first in the range.
- HLOOKUP is not dynamic because inserting a row between the rows in the range doesn’t automatically update the row index.
Frequently Asked Questions (FAQs)
• VLOOKUP requires you to point to a specific array. When you need to expand, add more data, or pull values from a new column, you’ll have to readjust all your formulas, but this isn’t the case with INDEX MATCH.
• One issue with using VLOOKUP is that it uses a static column reference, while INDEX MATCH uses a dynamic column reference, which leads to fewer errors.
• Another benefit of INDEX MATCH is that you can add new columns whenever you like without changing your lookup results. Lastly, INDEX MATCH is faster and doesn’t affect your computer’s performance
HLOOKUP stands for “Horizontal Lookup.” This function searches for a value from the first row of a table and returns a value from a specified row in the same column. This function requires the data to be organized horizontally across rows. It is used when the data is in rows, and you must look up values across columns.
VLOOKUP stands for “Vertical Lookup.” This function finds a value in the first column of a table and returns a value from a specified column in the same row. It requires the data to be organized vertically. It can be used when your data is structured in columns, and you must look up values across rows.
The main difference in syntax is that we use the row_index_num for HLOOKUP and the col_index_num for VLOOKUP.
Some of the advantages of XLOOKUP over VLOOKUP include:
a) XLOOKUP is used for bidirectional lookups and can search on both left and right sides.
b) By default, it gives an extra match and hence, you need not specify that parameter.
c) Eliminates the need for the range lookup parameter for exact matches.
d) XLOOKUP in Google Sheets can return multiple values at once.
Download Template
This article must help understand Alternatives to VLOOKUP in Google Sheets with its examples. You can download the template here to use it instantly.
Recommended Articles
This has been a guide to What Is Alternatives to VLOOKUP in Google Sheets. We will look at all the alternatives to VLOOKUP such as XLOOKUP with examples. You can learn more from the following articles –
Leave a Reply