Many of
our clients use the Excel LOOKUP function to link data from
CDXZipstream or CDXStreamer to other data residing in their
worksheets. For instance, demographic data from CDXZipstream
can be linked via zip code to other customer data covering buying
habits, return history, social media participation, etc. The
VLOOKUP formula takes the form:
=VLOOKUP(Lookup_value,Table_array,Column_index_number, Range_lookup)
where
Lookup_value =
The reference value to search for in the first column of the table
array. For the example above, this can be the actual value of
the ZIP code,"07869" or its cell address "A1".
Table_array =
The range of data of to be searched. The first column on the left of
the Table_array must contain the reference values being searched, in
this case ZIP codes.
Column_index_number
= The column within the Table_array that contains the data that will
be returned by the function. For the example here, this could
be demographic data. If column_index_number = 3, for instance,
the data resides in the third column counting from the left.
Range_lookup =
True or False. True looks for an approximate match, and False
looks for an exact match. You should almost always specify
False.
VLOOKUP can be
a bit tricky to use, however. Here are three common issues in
using this function.
1.
VLOOKUP will only work when all reference values are similarly
formatted. CDXZipStream always returns data as text, so an easy
approach is to force all ZIP code reference values into text by using
the Excel TEXT function. For example, ZIP codes can be
converted to text by using the formula =TEXT(A1,"00000"),
where A1 is the first cell location of ZIPs. You can then copy
this formula along any list of ZIP codes and use the results as
reference values. Note that simply formatting data as text
using cell formatting will unfortunately NOT usually work for
VLOOKUP.
2. The data
you're searching should not contain any leading or trailing spaces,
or non-printing characters. Use the Excel functions TRIM and
CLEAN to remove unwanted spaces and non-printing characters,
respectively.
3. Use
absolute referencing, or a named range, to define your table.
If the table array is defined with relative referencing, like
A1:D100, it will change as you copy it along your list. To
avoid this use absolute referencing ($A$1:$D$100), or use the Excel's
Name Manager (on the Formulas tab) to give a name to your table
array.
For more
information, please refer to the following blog article showing an
example of using CDXZipStream with VLOOKUP: ZIP Code Radius Analysis for Multiple Areas.
 Â
