Welcome back to AIMCLEAR‘s Deep in the Sheets! In this episode, we organize a list of leads and zip codes, DITS Vlookup, into specific geographic locations using VLOOKUP formulas. Save hours of time when determining the city and state associated with each zip code.
Follow along as we apply VLOOKUP formulas to PPC data to transform a clutter of numbers into meaningful data.
Read on for the full transcript:
Hi everybody, Dion here. This week on Deep in the Sheets we’ll be looking at VLOOKUP formulas. [MUSIC PLAYS] We have here a list of leads and with these leads we have ZIP codes, but what we don’t have is the city and state associated with those. To find those, we’re going to look up the ZIP codes for each lead in this big database of ZIP codes I have here. You can see here there’s 38,000 of them. So, to make this easier we’re going to use a VLOOKUP, which is just equals, VLOOKUP. First thing we’re gonna give it is the data source we’re going to be looking for. So in this case is our ZIP code. After that we’re gonna give it the location of the ZIP code and the data-set; the table array. So, we have our ZIP code here, as well as our city state and county. So I’ll give it that entire data-set. And then this third option here is going to be which column is returned. Since the formula we’re on—the column we’re on is city, so I’m going to give it the second column. So it’s going to look for the ZIP code and return the second column. So I’ll put a 2 in there. And the last thing is the approximate match or exact match. Exact match would be—it has to find the exact ZIP code in the data array and approximate match it goes alphabetically or ascending or descending. So I’m gonna give this FALSE, so it has to find the exact ZIP code, and hit enter. And you can see, it already found this ZIP code is in Bowie. So I’m gonna bring that down, and it should fill out our leads with the appropriate city. Next thing we’re gonna do is state, so we’re gonna do another VLOOKUP, then we’re gonna give it the ZIP code again, we’re gonna point to our data array. Same process here. We’re gonna give it the third column which is our state, and this will also be FALSE so it has to look for the exact ZIP code. Drop that down, and now we can take both of these, copy them, and paste that as data. And that’s all it takes to get our ZIP codes brought into city and state. That should do it for this week’s Deep in the Sheets.
