Excel’s Index and match functions used together can solve complex lookup problems.

Let’s jump right into an example that returns the distance between two cities that the user chooses in cells A2 and B2. The answer is in cells C2 and repeated in cell C3.

Index Function

For a brief illustration of the Index() function, have a look at the post called Excel Extract Nth Word from a Text String.

Match Function

The MATCH() function has the following syntax: MATCH(lookup_value, lookup_range, [match type]). The lookup_value is what you are looking for in the lookup_range. The lookup_range must be either a row or a column. The lookup_type in our example is set to zero. That means we are looking for an exact match. For more information on the index types, consult Microsoft.

More Information

Here’s an article about INDEX and MATCH at ExcelJet called INDEX and MATCH with multiple criteria.