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.
MATCH is an Excel function used to locate the position of a lookup value in a row, column, or table. MATCH supports approximate and exact matching, and wildcards (* ?) for partial matches. Often, MATCH is combined...
The Excel INDEX function returns the value at a given position in a range or array. You can use INDEX to retrieve individual values or entire rows and columns. INDEX is often used with the MATCH function, where MATCH locates and feeds a position to...