VLOOKUP
- A Handy Tool for Finding Data
VLOOKUP is an
Excel worksheet function that a lot of Excel users might not be
familiar with, but it's a great tool when working with lots of
address or other geographic data. It is simply a way to look up data
in a worksheet, and we've used it previously in our blog articles to
do things like compare lists of ZIP codes, identify ZIP codes in
overlapping regions, and look up associated data when using the
CDXClosestZip function.
Let's look at an example where we have a set of
data in an Excel worksheet with customers and their addresses:
If we have a
second list of random customer names, such as customers who purchased
merchandise in the last month, and want to look up their ZIP codes
using the data above, we can use the following worksheet equation:
= VLOOKUP( F1,
$A$1000:E$1000,5,False)
where:
F1 is the cell
address of the first customer who purchased merchandise in the last
month
$A$1000:$E$1000
is the worksheet range of the customer address table above
5 is the
column number of the data set (counting from the first column of data) where
our desired data (ZIP codes) is located
False
indicates we want an exact customer name match when searching the
table
If the first
customer in the list is K. Suarez, the VLOOKUP equation will return
the ZIP code "08361". To return ZIP codes for all
customers, just copy and paste the equation down the entire list.
For VLOOKUP to
work correctly, data types must match. You can use Excel VALUE
or TEXT worksheet functions to make sure this is the case. To see an
example of this, and also see VLOOKUP in action, watch the
tutorial Find
ZIP Codes in a Radius.
