You can transform Match function to Vlookup or Hlookup in Google Sheets. I mean use Match as an alternative to Vlookup or the Hlookup functions.

Most probably I am the first one sharing this tip online. In my research, I don’t find anybody is using such a trick so far.

You may find the use of Index-Match combination. Here I am not using the Index function. So my formula is most probably a unique one.

Does this Work in Excel?

Yes! You can use Match as an alternative to Vlookup or Hlookp in Excel too. In both the cases, the procedure is the same.

As a Google Sheets user, I am detailing the steps with the help of Screenshots taken in Google Sheets, but applicable to Excel users too.

Usually, the Match function returns the relative position of a search key in a horizontal row or vertical column.

But using the Indirect, not Index, and Char functions you can transform Match function to Vlookup or Hlookup.

Actually, in Vlookup, I mean the Math as Vlookup, the use of Char function is not necessary.

Match can search a key and return not only its relative position, but it can also extract value from the row (Vlookup) or column (Hlookup) where the search key is found.

Is there any benefit of using the said Match function as an alternative to Vlookup or Hlookup?

Yes! it’s beneficial in two ways.

1. The Match + Indirect combo can lookup left or right of a search key.

2. Useful in Conditional Formatting (Where Vlookup may miserably fail).

Let’s see how to transform Match Function to Vlookup or Hlookup in Google Sheets as well as in Excel (again I am telling you the formula and steps are the same in Excel and Google Sheets).

After that, I will explain to you the above two points (benefits) of using Match as an alternative to Vlookup.

How to Transform Match Function to Vlookup or Hlookup in Google Sheets / Excel

Here is the step by step instructions to use Match combo as an alternative to Vlookup.

Sample Data: Apple Production by Countries

Data Range: B4: C18

Source: Wiki

This data is imported to Google Sheets using the IMPORTHTML function for testing the Match function.

It won’t work in Excel. So if you are using Excel, please either type the data or import the data to Google Sheets and then copy to Excel.

Here is the formula that I have used in cell B4 in my Google Spreadsheets to import this data for testing.

=IMPORTHTML("https://en.wikipedia.org/wiki/List_of_countries_by_apple_production","table",1)

Screenshot:

Here is the step by step instructions to transform Match function to Vlookup. Then I will take you to the steps involved in Hlookup. It’s not a complicated one. So please read on.

Steps to Transform Match Function to Vlookup in Google Sheets/Excel

Purpose: Lookup the search key “France” in column B and return its “Apple production (tonnes)” from column C. The result must be the value 1,819,762.

Here is conventional Vlookup Formula:

=VLOOKUP(E4,B5:C18,2,0)

The Transformed Match Formula:

=INDIRECT("C"&MATCH(E4,B1:B18,0))

As I have mentioned above this Match combo formula has some advantages over the Vlookup.

No doubt Vlookup is more powerful and its capability to return an Array result is unparallel.

Must Read: How to Use Vlookup to Return An Array Result in Google Sheets.

The Match Formula Explanation and Steps:

Step 1: The use of Match function alone in Google Sheets in the above dataset.

See the below Match formula example which returns the relative position of the search key “France” in the range B5: B18.

To transform Match function to Vlookup, the first step is to find the row number, not the relative position of the search key.

How to Convert Relative Position to Actual Row Number?

In the Match formula, the range is B5: B8. Just change the range to B1: B8. It will convert the relative position returned by the Match to Row number.

=MATCH(E4,B1:B18,0)

This formula will return 13, that is the row number of the search key “France” in column B. This is the Step 1.

Step 2: Transforming Match to Vlookup.

We want the formula to look up “France” and return its “Apple production (tonnes)” in Column C.

Actually what we want is to extract the content in the cell C13. Please refer to my sample data above.

So just prefix this column letter to the Match output which is the row number 13.

="C"&MATCH(E4,B5:B18,0)

Result: C13

Step3: Indirect this cell Address.

=INDIRECT("C"&MATCH(E4,B1:B18,0))

Done!

Steps to Transform Match Function to Hlookup in Google Sheets/Excel

Here the steps are little different because in this we are using Match in a row.

The below Match formula will return the relative position of the search key “France” in the row.

Here again, we can modify the data range in the Match formula to return the column number of the search key.

Here the Match formula returns 10 which is the relative position. I have already shared with you how to convert relative position to row number above.

Here we can convert relative position to column number as it’s a horizontal match.

Here is that formula. Just change the lookup range from B4: P4 to A4: P4.

=match("France",A4:P4,0)

This formula returns 11 which is the column number. You can convert a column number to column letter in Google Sheets using the Char function as below.

Just use=Char(64+1) to return the column letter A. If you replace 1 with 26, it would return Z.

That means we can add the column number to get the Column letter. So the formula would be.

=Char(64+match("France",A4:P4,0))

Result: K

Note: This formula has a limitation. It would only work up to column Z. If your data range is larger, then you might use this alternative one. This only works in Google Sheets.

=regexreplace(address(1,match("France",A4:P4,0)),"[^A-Z]","")

In this, the Match formula is surrounded by other elements that take care of the role of Char function.

Similar Tutorial – How to Autofill Alphabets in Google Sheets

The value to extract is in row # 5. That means you want the value that in cell K5. So join 5 to the above formula.

=char(64+match("France",A4:P4,0))&5

Then Indirect it.

=indirect(char(64+match("France",A4:P4,0))&5)

You have learned two cool tips that to transform Match function to Vlookup and Hlookup in Google Sheets/Excel. Have you seen this in use earlier?

Transform Match Function to Vlookup – Benefits

Here I am not going to Hlookup as Vlookup is the most common in use.

I have already mentioned the benefit of using Match function for Vertical/Horizontal lookup above. There are two visible advantages.

Lookup Left of the Search Key Using Match

In this, I want to find the rank of “Student 3”. The search key “Student 3” is in column E and the rank is column D which is obviously the left-hand side of the search column.

Of course, you can use Vlookup itself or Indirect-Match in this case. I am not denying that. But this Match use is pretty cool.

Conditional Formatting

Since we generate the cell address and then Indirect it, it will come in handy in Conditional Formatting.

See this advanced tutorial which sheds some light in that direction – Highlight Intersecting Value in Google Sheets in a Two Way Lookup.

Hope you could learn how to transform Match Function to Vlookup/Hlookup in Google Sheets and as well as in Excel.

Learn this cool tip yourself first and then share this new info with your friends. Enjoy!