Index Match is a better alternative to Vlookup and Hlookup in Google Sheets. Don’t mistake that Index Match is one simple function. It’s INDEX and MATCH, two different Google Sheet lookup functions. You can use INDEX and MATCH functions in combined form to use it as a better alternative to VLOOKUP and HLOOKUP.
Before going to tell you what are the benefits of using Index Match over Vlookup and Hlookup functions, let us see how to use them independently. We are going to learn Index, Match, Vlookup and Hlookup functions all at one place.
1. How to Use Google Sheets Index Formula
The basic purpose of Google Sheets Lookup formulas are to return content of a cell based on a search key or offset number.
In Index formula, we can use offset row and column numbers to return a specific content of a cell.
Sample Usage of Google Sheets Index Formula:
INDEX(A1:C10, 5, 1)
Where A1:C10 is lookup range, 5 is row offset and 1 is column offset
This formula returns “Flashback Arrester” as result.
The above formula and image can simply teach you how to use Google Sheets Index formula.
2. How to Use Match Formula in Google Sheets
Match is another lookup formula in Google sheets that we can use independently or in tandem with Index. Just like Vlookup and Hlookup, you can use Match formula for vertical or horizontal look up.
2.1. Vertical Use of Match Formula in Google Sheets
Unlike Index, this Match formula returns a number. It is obviously the relative position of a look up item.
Where G is search key, A2:A7 is lookup range, and 0 is to indicate that the data is not sorted.
This formula returns 5 as result.
See the range “C2:C7” above. You can use any single column range only. Another example to single column range is “A2:A7”. When you want to use Single Row in Match formula, use it as below.
2.2. Horizontal Use of Match Formula in Google Sheets
When you have horizontal data set, you should use the Match formula horizontally. Here is the example. It’s similar to vertical use. So I’m not going to the detail.
As a side note, I’ve just used Google Sheets Transpose Command to change the direction of the data set. This is the same sample data which we earlier used.
Learn the above two function thoroughly. Then move to the below Index Match combined use.
3. Index Match – How that Combination Works
Here is the Index Match combination trick. I have used the above two formulas here and compared.
In the above Index formula, you can replace the number 5, which is row offset with Match Formula as Match formula returns a number.
4. How to Use Google Sheets Vlookup and Hlookup Formulas
4.1. Vlookup or Vertical Lookup Formula
Google Sheets Vlookup formula is very easy to use compared to the above Index Match functions.
What it does? Vlookup formula searches down the first column of a range for a given search key. Then it returns the value of a specified cell in that row.
Where “G” is the given search key, A2:G7 is the range, 3 is the column index and 0 to tell that the data is not sorted. Here you should remember two points.
- Vlookup looks for the given key in the first column of the given range. Here the range is A2:G7. Here Vlookup looks for the key in the range A2:A7 only.
- Column Index 1 means Column A, 2 for column B and so on.
4.2. Hlookup or Horizontal Lookup Formula
Similarly above you can use Hlookup formula for horizontal look up.
Once learned, move to the below tips.
5. Index and Match Formula Combination as an Alternative to Vlookup and Hlookup
Now, I will reveal that trick. Actually Index function is equal to Vlookup as both return value of a specified cell. Then why we require Match function in between? To know that you should understand where the Index and Vlookup difference lie.
5.1. Difference between Index and Vlookup Formula
Both Index and Vlookup can return the same result. For example see the below image. Both return the result “Flashback Arrester”.
As already told above, Vlookup and Index can return the content of a specific cell. But the approach is different. Vlookup search down first column of the given range, “A2:G7”, for key “G”. In index there is no key. It simply uses the offset row and column feature. Instead of search key “G”, you can specify row to offset which is 5 in the Index formula above. Rest are same.
5.2. Difference between Index and Hlookup Formula
It’s similar to above Index Vs Vlookup. So let us straightaway go to the example.
Now it’s time to tell you how to use Index Match combination as an alternative to Vlookup and Hlookup formulas.
6. How to Use Index Match as an Alternative to Vlookup
From the above examples you can understand that Index is similar to Vlookup as well as Hlookup. The only thing Index can’t do is, it can’t accept search key as it only accepts row or column offset. Here comes the importance of Match formula.
Match formula can accept search key similar to Vlookup or Hlookup. Match formula accepts search key and return relative position. That means indirectly we can use search key in Index formula with the help of Match formula. So, see how this Index Match combination work to replace Vlookup here.
The above formula can tell you a whole lot of thing. Learn the usage and move to the next tips.
7. How to Use Index Match as an Alternative to Hlookup
Now the same way, we can use Index Match for Horizontal Lookup. I am not going to much detail. Check the screenshot below where I’ve marked everything to clear your doubts.
8. Advantage of Index Match Over Vlookup and Hlookup
This is the final part. Why should one switch to INDEX MATCH from Vlookup or Hlookup formulas?
Most of the Google Sheets as well as Excel users aware of the use of Vlookup function. I think it’s because of the mouth publicity Vlookup gets among spreadsheet users. So I think the importance of Index and Match functions are undermined somewhat.
Now to the point. Instead of telling you what Vlookup can do, I will tell you what Vlookup can’t do.
Point No. # 1
Vlookup formula cannot return value left to the search key. See the below example. Here lookup value or key is “Ann Rivera”. There is no option in Vlookup to return a value left to the lookup value. We did it with the Index Match formula below.
Here we want to know the January sales value of Ann Rivera. With Index Match combo, we addressed this issue.
Point No. # 2
The below Vlookup formula looks down the search key “Product 4” and return the result in the same row from column 4, i.e., 1396.00. The problem here is, when you insert a new column between column 1 and column 4, you will get a wrong answer. If the new column is just left to the column 4 and it’s blank, you will get no results.
Again Index Match combo formula can solve this issue. See the alternative Index Match formula below.
In the above case you can also use Google Sheets Count functions together with Vlookup. See that tricky combination below.
I think it’s time to wind up this tutorial. The above disadvantages of Vlookup against Index Match combination are applicable to Hlookup too.