Hi - Dave here.
Happy Friday!
Last week I sent out a
more advanced INDEX and MATCH example. The formula works well but is hard to understand if you don't already know how to use INDEX and MATCH.
To help explain how INDEX and MATCH work together, here is a more basic example that looks up the year, rank, and sales for a random group of 1990s movies.
This is a classic "exact match" lookup based on the movie title. The gist is that the MATCH function locates the position of a movie in the list and feeds that position into INDEX as the row number. The INDEX function then returns a value at that row number.
[
Download the workbook and read the full explanation]
The INDEX and MATCH combo is flexible and powerful, and there are different ways to retrieve information once MATCH has determined a location. In the latest version of Excel, you can even retrieve year, rank, and sales
all at once in a single formula. Click the link above to see the options and try it out yourself.
For a complete introduction to INDEX and MATCH, with several examples and video links, see this overview:
How to use INDEX and MATCH
Excel formulas
We maintain a list of over 500 working formulas
here.
If you need more structure, we also offer
video training.
Have a great weekend!
Dave
