Learn How To Use Index And Match Functions In Excel

Sat 23rd April 2011

Discover how to use INDEX and MATCH functions to create a very flexible lookup

If you've used VLOOKUP in Excel you'll know that you can match an index value with the values in the first column in a data table and then specify the column number in the data to extract the required value. Do you know you can go one better than this and look in ANY column of the data for a match and then extract the required data. This article describes how to use the Excel Index and Match functions to do just this.

Using the Match Function

The Match function is used to determine the position of an index value in a column of data. Suppose for example we have the values xx, yy and zz in cells D1,D2 and D3. Then we type in the value yy into cell B1 as our index. We'll enter the Match function in cell C1. So in C1 we type =MATCH(B1,D1:D3,0) and press Enter. Cell C1 then shows the value 2, because yy is in the second row from the top, in the specified range. The zero after the comma at the end of the function is to select an exact match to the index. So the purpose of the Match function is to return a number corresponding to the row position of the matched value is in the specified column of data.

Using the Index Function

The Index function is used to return a value from a certain column of data, in a particular row. So keeping with the above example, if we type in Cell E1 this function =INDEX(D1:D3,2) and press Enter, we'll see the value yy in the cell. This is because in the cell range D1 to D3 the second row (from the top) contains yy. So the purpose of the Index function is to return a value from a specified column range and specified row number.

Combining Index and Match Functions

Combining these two functions allows us to create a lookup more powerful than VLOOKUP, because we can look for a match in any column of data, not just in the first column. We first use Match to find the row number in the specified column where the value matches the index. Then we use Index to look in a different column (in the same table) and use the same row number produced by Match to extract the required value. So combining Index and Match functions allows us to search for an index match in any column we choose and extract data at the same row position in a different column.

We'll now describe an example using these functions. Suppose we have a four column table with headings "ITEM, COLOUR, CODE and COST" in cells D1, E1, F1 and G1. We then enter these four records in separate rows under the headings as follows. First record is Product1, Red, A1, £5.00; Second record is Product2, Green, A2, £6.00; Third record is Product3, Blue, B1, £2.50; and Fourth record is Product4, Red, B2, £4.00. So we have created a data table with four headings and four records.

Suppose we want to be able to type in a product colour in cell B1 and have Excel show the product cost in cell H1. By the way if more than one product match the index colour, Excel will return the cost of the first matching product. We type the index value, Red, in cell B1 and we want to show the cost using the functions in cell H1.

Remember the index value is in cell B1, the colours are in cells E2 to E5 and the costs are in cells G2 to G5. We want to extract a cost from G2:G5 matching our index value. So in cell H1 we type =INDEX(G2:G5,MATCH(B1,E2:E5,0)) and press enter. You'll now see in cell H1 the cost of the first Red item in the list, £5.00.

Want to use more than one index in the lookup?

The combination of the Index and Match functions can also be used to match more than one index in the target data. For example suppose we want to find the cost of Red products with Code B2. We'll type in a second index value B2 in cell C1. Because we need to now search in more than one data range for matches, we need to amend our formula to allow for two indexes and two index ranges to search for matches in. So we edit the formula in H1. You might recall that the code values are in cells F2 to F5. In the revised formula we need to use the ampersand symbol "&" to allow matching of more than one index values and more than one index ranges.

So we amend the Match part of the formula to look for a match for indexes B1&C1 and look for a match in ranges E2:E5&F2:F5. The amended formula will not work yet, but suppose we try the formula =INDEX(G2:G5,MATCH(B1&C1,E2:E5&F2:F5,0)) and press Enter. Unfortunately this generates the error message #VALUE. We need to change the formula to an array formula to enable it to search in more than one data range at a time. To do this ensure cell H1 is still selected and click once into the formula edit bar. Don't press the Enter key. Instead press Shift+Ctrl and Enter key at the same time.

This action places curly brackets round the formula and converts it to an array formula. Now the cell will show the correct cost for the Red B1 item. You might like to try different combinations of colour and code in the two index cells B1 and C1 to versify this works.

Interested in learning more about Excel Lookup functions? A really effective way is to attend a training course. There are many available and the best ones offer lots of hands on experience, which can really help enhance your Excel skills.

Original article appears here:
https://www.stl-training.co.uk/article-1621-learn-how-use-index-and-match-functions-in-excel.html