98.8% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsPrevious article Next article Excel articles
Learn How To Use Index And Match Functions In Excel
Sat 23rd April 2011
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
London's widest choice in
dates, venues, and prices
Public Schedule:
On-site / Closed company:
TestimonialsCDP WorldWide (Europe) gGmbH
Senior HR Advisor Subhalaxmi Panda Excel Advanced You are the best. :) Experian
Head Of Marketing Gabriella Neudecker Excel Intermediate Tailor to the discipline very good, enjoyable River Publishing
Acting Editor Rosie Mullender Excel Introduction This was the best training course I've ever been on - the pace was adapted perfectly to our learning speed, and everything was explained really clearly. I felt able to ask questions, and Richard was really enthusiastic. Excellent stuff. |
PUBLICATION GUIDELINES