How do you use index and match function in Excel?

Answer 1

The Index function finds the value in a list or table at the position given. So if you have a list or a table or even a series of tables, you can specify a position in it and it will return the value at that position. So if you have a list of values from cell A2 to A20, and wanted to find what was in the third cell, you would type the function like this:

=INDEX(A2:A20,3)

IF you have a table, you would use a row and a column position, in the 4th row and 3rd column in a table from A2 to D20, you would do it like this:

=INDEX(A2:D20,4,3)

You can even have several tables and specify which one to search in. You can specify the ranges inside brackets, then specify a row, column and which area to choose from:

=INDEX((A2:D20,C29:H40),4,3,2)

The Match function is used to find the position of a value in a list of values. So if you have a list of cities, including New York in the cells from A2 to A20, the Match function will search through the list and find which position it is in.

=MATCH(A2:A20,"New York",0)

It can also be used to find a close value if there isn't a perfect match, either the lowest number above it by specifying -1 where the zero is, or the highest number below it by specifying 1 where the zero is. That number can be left out altogether, in which case it assumes that you want to use the 1 setting. You also need to have the values in order, either ascending if you are using 1 or descending if you are using -1.