Index Match – Better Alternative to Vlookup and Hlookup in Google Sheets

0
335
Index Match - Better Alternative to Vlookup and Hlookup

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.

How to Use Google Sheets Index Formula

The above formula and image can simply teach you how to use Google Sheets Index formula.

Learn advanced use of Index Function

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

Sample Use:

Unlike Index, this Match formula returns a number. It is obviously the relative position of a look up item.

MATCH(“G”,A2:A7,0)

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.

simple vertical use of Match formula in Google Sheets

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.

horizontal lookup using match formula in google sheets

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 Advanced Use of Match Function.

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.

Sample Use:

vlookup(“G”,A2:G7,3,0)

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.

  1. 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.
  2. Column Index 1 means Column A, 2 for column B and so on.

Example to Google Sheets Vlookup formula

4.2. Hlookup or Horizontal Lookup Formula

Similarly above you can use Hlookup formula for horizontal look up.

horizontal lookup formula example

Learn advanced use of Google Sheets Vlookup and Hlookup Functions.

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”.

Difference between Index and Vlookup Formula

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.

Index Match vs Vlookup

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.

INDEX MATCH Vs Hlookup

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.

=index(D3:D8,match(“Product 4”,A3:A8,1),1)

In the above case you can also use Google Sheets Count functions together with Vlookup. See that tricky combination below.

=vlookup(“Product 4”,A3:I8,(countblank(A6:E6)+counta(A6:E6)))

I think it’s time to wind up this tutorial. The above disadvantages of Vlookup against Index Match combination are applicable to Hlookup too.

LEAVE A REPLY

Please enter your comment!
Please enter your name here