Retrieve demographic data with VLOOKUP

In this tutorial, we'll explore the capabilities of the VLOOKUP function to do data analysis using our US Cities Database. VLOOKUP, a powerful Excel feature, enables precise data retrieval from tables or ranges based on specific row criteria. Specifically, our focus will be on a set of cities, aiming to retrieve essential data points such as median household income and the percentage of individuals with college-level education or higher. All of these data fields are included within our US Cities Database. Now, let's dive into the step-by-step process:

1. Start by opening the US Cities database. Create a new sheet where you'll list the cities you wish to analyze. Here's a sample setup illustrating my city list with corresponding state IDs.

2. Because some cities have the same name but are in different states, we want to be sure we're looking at the right city. So, we'll add the state initials to the city name. We'll create a new "CITY + STATE" column that combines both. To do this, we'll use a special symbol (&) that lets you join text. This step prepares the data for precise VLOOKUP matching.

3. In situations where the same city name appears more than once within a single state, we will create an "Occurrences Count" column. This column will provide insight into the number of matches within a state.

4. Now, we'll do the same trick with the US Cities database. We'll create a new column where we combine the city name and the state id.

5. Navigate to your table, find the column labeled "income household median," and then start typing "=VLOOKUP("

6. The VLOOKUP function works by using four pieces of information, which you should provide in the following order:

   • First, you give it the value you want to look for. In our case, it's "Hollins-VA" in the "CITY + STATE" column.
   • Next, you tell it where to search for this value. In our case, we're looking in the US Cities Database column, specifically from the combined "city + state" column down to the "income household median" column. This shows the VLOOKUP function the area where it should look for the information.
   • Now, you need to tell VLOOKUP which column contains the information you want it to bring back. For us, it's the column numbered "27" if we start counting from the "city+state" column and move over to the "income household median" column. This is like pointing to the right spot on a shelf to get the exact thing you're looking for.
   • Lastly, we choose "False" to make it clear that we're looking for an exact match.

7. The remaining rows will automatically populate with this information. In case this doesn't happen, a simple solution is to drag the formula downward.

8. Let's use the VLOOKUP formula once more to discover the numbers for the percentage of people who have gone to college or have higher education. When you're picking the area to look at, make sure you drag your selection to include all the cells up until the "education college or above" column. Also, remember this time we'll be using column number 32 instead of 27 for the "education college or above" data.

9. To find out how many times the same city name appears in a state, we can use another tool called the "COUNTIF" function in Excel. To use it, type "=COUNTIF(". In its basic form, COUNTIF works like this: =COUNTIF(Where should it look?, What should it find?) In our case, it will look something like this: "=COUNTIF('US CITIES DB'!B:B,[@[CITY + STATE]])". This helps us keep track of how often the same city appears in a state. Note: Take a look at the image, and you'll notice that the city of "Washington" appeared 23 times in Virginia. This might suggest we need to do some additional filtering to ensure we're pinpointing the exact city we're aiming for.

If you encounter any difficulties or have questions, please don't hesitate to reach out to us at [email protected]. Our dedicated support team is here to assist you.