Quick, clean, and to the point

How to look things up with INDEX and MATCH

In this video, we're going to combine INDEX and MATCH together to look things up.

Here we have the city population data we looked at before. We used the INDEX function to retrieve information about a city with a hard-coded position value.

When we supply a number, INDEX retrieves information for the city at that position in the list.

Now instead of fetching information by position, let's convert this worksheet so that we can retrieve information by city name. I'll do this my using the MATCH function to find the position of a city in the list and using INDEX to retrieve information at that position.

First I'll move city above position, since that's what we'll use to lookup information.

Next, I'll clear out existing formulas so we can start fresh.

As a reminder, we already have two named ranges here: one for the city data, and one for position.

I'm going to add another named range called city_names.

Now I'll enter a city name in H7 so the formulas have something to work with.

INDEX is going to need a position, so let's calculate that next using MATCH. The lookup value is the city name in H7, the lookup array is city_names, and the match type is zero, for exact match.

Now MATCH calculates the position of whatever city we enter.

The next step is to use INDEX formulas to retrive the other data. For State, the array is city_data, the position is the value we calculated with MATCH, and the column number is 2.

I can copy that formula down and adjust the column numbers for Population and Area.

Now when I enter a new city name, INDEX and MATCH work together to retrieve the relavant information.

It's not much fun typing in the city name, so let's make that easier using data validation. I just need to allow a list, and, since we already have a range name for city_names, I can just use a formula that refers to that range.

Now I can choose any city from a built-in drop down list.


Related shortcuts

Dave Bruns
Excel foundational video course
Excel Pivot Table video training course
Excel formulas and functions video training course
Excel Shortcuts Video Course
Excel Charts video training course
Video training for Excel Tables