Using MATCH and INDEX

Hello. This article is about looking up values in Excel.

Balaji asks:

How can I create a Query that displays maximum value in one column and the date corresponding to the maximum value in the adjacent column ?

 

There are several ways to do this. In this video, I will have a look at the MATCH and INDEX formulas to solve this problem.

To have a look at these functions, please watch the video below.

(If you want the video in a bigger window, then the “full screen” button is on the bottom-right of the video.

So let’s have a look at those two functions in turn.

First of all, MATCH looks for a matching value in a list. Its syntax is as follows:

=MATCH(value, list, 0)

There are three arguments:

  • The first is the value you are looking for. In the example in the video, I was looking for the maximum value in column C, so I used =MAX(C2:C4)
  • The second is the list that you are searching through. In my video, I was looking through C2:C4.
  • The third indicates whether you are looking for an exact match, or an approximate match. This video is all about looking for exact matches, so I used a zero as the third argument.

Remember, the MATCH function returns a position in a list, e.g. 1, 2 or 3. The next stage is to look through another list to get the corresponding value.

For this I use the INDEX function. (There are other functions, such as INDIRECT or OFFSET, that I could also use). The syntax for the INDEX function starts of as follows:

=INDEX(list, index_position)

There are another 3 arguments, but they are all optional, and I don’t need them for this example. Let’s have a look at the arguments:

  • The first is the list you are looking through. In this video, I used column C for MATCHing, but I want to retrieve the corresponding value in column B, so I use B2:B4.
  • The second is the index position you are looking for. In this video, I use the results of the MATCH function for that. So, if I am looking for the item in the second row, then this argument needs to be a 2.
  • The third, optional, value, is the index position in terms of columns.

So you can combine MATCH and INDEX together to create the equivalent of a VLOOKUP. However, a VLOOKUP function requires the answer to the right of the value you are looking up; if it is to the left, it won’t work. MATCH and INDEX together gets around this problem.

Thank you very much for reading, and please look at answers to other questions.

If you have a question, why not post a comment below.


 

If you would like to know more about Excel, why not take one of my courses – and using the coupon code SQLINTRO will reduce the price to only $10 per course.

The above functions are included in my Microsoft Excel – from Intermediate to Specialist video. This takes you from level 5 all the way to level 10 in 10 hours.

Alternative, my course Microsoft Excel – go from Beginner to Specialist certificate takes you through the first 5 levels of Excel functions in a little over 11 hours long.

Leave a Reply

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