How To Look Things Up With Index And Match

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 to retrieve information by city name. I’ll do this by using the MATCH function to find the position of the 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 be using to look up 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 write INDEX formulas to retrieve the other data. For State, the array is city_data; the row is the position 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 relevant 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 easily choose any city from a built-in drop down list.

Dave Bruns

Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.