Jul 03, 2014

“Searches for a value in the first column of a table array and returns a value in the same row from another column in the table array.”

Basically, when given a range of data, you can use the function to search the first column of that data and if a match is found, return a related column. In real terms, this lets you find an address when given an invoice number, find a quantity when given a product code, and so on. That’s great, and both VLOOKUP and HLOOKUP (the vertical and horizontal versions) work fantastically well. However, what if the data you want to search for isn’t in the first column? You’d like to be able to search any column for a value and also to be able to return any column for the matching row. I’d like to introduce you to “INDEX” and “MATCH.” According to Excel, the MATCH function does the following:

“MATCH(lookup_value,lookup_array,match_type): returns the relative positions of an item in an array that matches a specified value in a specified order.”

The match type can be one of ‘Less Than,’ ‘Exact Match’ or ‘Greater Than.’ By way of example, assume the following layout:

Or even something a little more random, such as:

If I wanted to know which row of the range the value ‘Apr’ in the first example could be found, I could enter the following formula:

=MATCH(“Apr”,A2:A5,0)

It would return A4, as in the lookup array of A2:A6, the value is the fourth in the list. Similarly, for the second example, if we wanted to find the value ‘P954’, the formula would be =MATCH(“P954”,A2:A10,0) and the result would be a 7 as it is the seventh value in the list. It’s interesting to note that MATCH will only allow you to enter a lookup array than spans a single column (or row) and selecting an array that is multiple columns or rows will return the ‘#N/A’ error. So, let us turn our attention to INDEX for a moment and then we will return to MATCH shortly. According to Excel, the INDEX function does the following:

“INDEX(…): returns a value or reference of the cell at the intersection of a particular row and column, in a given range.

This tells us there are two ways of using INDEX. In this post, we are interested in returning a value of a particular intersection, which means we have to write the formula in the following format:

=INDEX(array,row_num,col_num)

Given an area of data, such as a contact listing:

Using a VLOOKUP, we could search only by extension and return a name or department. Using our INDEX function, we get to specify which column and row we require. So, if we wanted to return ‘Damon,’ we could use =INDEX(A2:C7,4,2). Similarly, if we wanted to return the extension ‘805,’ we could use =INDEX(A2:C7,5,1). Now, you might be wondering at this point “But Steve, if I need to know the exact row and column of a value, doesn’t that defeat the purpose of the formula?” Well, what it does tell us, is that when you want a value using INDEX, you do need to know the row and column, yes. However, think back to the VLOOKUP function. When using it, you need to know which related column to return, and the function determines the matching row based on the first column only. So, INDEX will allow us to be a little more flexible because we can choose any column to search and any row. By nesting the MATCH within the INDEX, we can initially emulate a VLOOKUP. So let’s say we wanted to return the name for extension 803:

The MATCH portion returns a 4 as the ‘803’ (the value entered into A10) is the fourth item in the array A2:A7. Thus, the INDEX function turns into INDEX(A2:C7,4,2). Let’s assume we knew the name (second column) and wanted to return the extension (first column) – this is beyond the means of a VLOOKUP.

By changing the columns we refer to in the MATCH portion, we can now search any column for a value and when a match is found, return any related column for that row! By adding another MATCH, you can make your searches dynamic, in that you would only need to change the column headings you were after (A9 and B9 in my above example), and the data searched for and returned can be easily changed by anyone using the sheet without them having knowledge at all of Excel. You could also extend the functionality by using tables, so the formulas utilise proper nouns and also by using Data Validation, so users are presented with a drop-down list of valid column names to search or return. I’ll leave this to your own experimentation for now! So, as you can see, on their own, MATCH and INDEX may not appear to be the greatest functions, however, when combined, they can really make your comparisons, validations, reconciliations and so forth, truly come to life and allow Excel to work for you!