Using MATCH and INDEX
Hello. This article is about looking up values in Excel.
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:
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.