How to look things up with INDEX and MATCH

Abstract

In this video we’re going to combineINDEXandMATCHtogether to look things up.

Here we have the city population data we looked at before.

We used theINDEX functionto 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 to retrieve information by city name.

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

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.

Now MATCH calculates the position of whatever city we enter.

The next step is to write INDEX formulas to retrieve the other data.

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

Now when I enter a new city name,INDEXandMATCHwork together to retrieve the relevant information.

It’s not much fun typing in the city name, so let’s make that easier usingData Validation.

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