Skip to content

The INDEX and MATCH Combination

This article shows you how the INDEX and MATCH functions can work together as a more flexible solution in place of the VLOOKUP and HLOOKUP functions.

The VLOOKUP and HLOOKUP functions locate a value based on another specified search value.

Let’s look at an example. Take a look at the dataset shown below.

The VLOOKUP function can be used to find and return the price of a specified food, such as apples.

But what would you do if you wanted to look up a specific food based on the price instead? You can’t use the VLOOKUP function because the VLOOKUP requires the search value to be in the first column of your dataset and nowhere else.

So what can you use instead? This is where the INDEX and MATCH combination offers a solution.

The INDEX Function

The INDEX function is not very complicated. It simply returns an item based on its relative position within a range.

Here is an example. Take a look at the data set below.

Now let’s say you want the INDEX function to return a price value of 2.25.

The range you are searching through is A2:D5. The value 2.25 exists in the second row and third column of this range. Therefore, you would write the INDEX function as follows.

The INDEX function will return the value in the second row and third column of the referenced range, and you end up with the value of 2.25.

The MATCH Function

The MATCH function looks for an item in an array (list of items) and returns that item’s relative position.

For example, let’s say you have the following list of colors.

  • Blue
  • Red
  • Green
  • Yellow

If you use the MATCH function and search for the color “Green,” the MATCH function will return a value of 3 because “Green” is the third item in the list.

Putting the Functions Together

It’s finally time to look at how these functions go together. Let’s take a look at our dataset one more time.

 

 

To replace the VLOOKUP or HLOOKUP functions and look up a food item based on price, our new formula will look like this.

=INDEX(A2:D5,MATCH(B7,C2:C5,0),1)

Reading our formula from left to right, we first come across the INDEX function. The first argument tells us the INDEX function is searching through our entire dataset.

A2:D5

The second argument specifies the row number the INDEX function will look in. This argument will be whatever result is returned by the MATCH function.

MATCH(B7, C2:C5, 0)

Let’s look at the MATCH function now. Here, the MATCH function is set up to only search through the list of prices.

C2:C5

It will look for the specified price value and return the row number it is found in.

MATCH(B7, C2:C5, 0) = 3

Going back to our INDEX function, we see the third and final argument is a 1.

The third argument is 1 because we want the INDEX function to return a food item. All of the foods are listed in the first column of our dataset. Therefore, we want the final argument to be a 1 to signal the INDEX function to look specifically in the first column.

Finally, putting it all together, we can see that the INDEX function first looks at the entire data set and returns the value that exists in row 3 and column 1.

That value is “Bananas.” As you can see from the table, “Bananas” are indeed the food with a price of 0.75.

This tells us that we have successfully put the INDEX and MATCH functions together to look up a food based on a specified price.

Now the next time you’re looking for a handy VLOOKUP replacement, you can pull out the INDEX and MATCH combination to do the trick!

This Post Has One Comment

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back To Top