Adding geocoding information with Vlookup

One of the problems you might run into while creating visualizations with Mapsdata is missing geocoding data (postcodes, coordinates, etc.). Sometimes the data you have, although it includes some form of location information, cannot be plotted on the map.

This may be the case if your location information is tailored to your own use, for example if your entries are categorized “Head office”, “West coast office”, “Factory 1”, etc. It could also be the case if the location information is quite specific and not one of the types recognized automatically by Mapsdata (see the recognized types of geocoding data). If the number of entries is small enough you can easily solve this manually, but for large amounts of data, the Vlookup tool in Excel is a great help.

If you’re interested, we also have a video tutorial on how to geocode data with VLOOKUP.

The help below is best tailored to users of Microsoft Office Excel but also applies to other spreadsheet software such as Apple’s iWork Numbers and Open Office’s Calc.


What to do:

First find (or create) a reference list containing each of your existing locations and their geocoding information. You can place it off to the side of your data in your spreadsheet.

In the example below, the spreadsheet lists events happening in different locations within a fictional company. On the right you can see the reference list we created which contains the geocoding information. Here we use ZIP codes, but of course the process is exactly the same with coordinates, and all the other geocoding formats which you can use with Mapsdata (click here to discover them).

If there are many different locations and they are not specific to you, you may be able to find a reference list somewhere on the web. This is what we did with our data visualization of the busiest stations of the London Undergound. We found a list of the coordinates of each station on OpenSteetMap.org, a great wiki-based project for free geographic data.


Once this is done, the next step is to create a new column for geocoding your data set entries, give it the appropriate name (here: ZIP), and fill it using your reference list. To achieve this, we ask the software to do the work and to find one piece of information, here the place “Head office” for example, in our reference list and then display the corresponding ZIP.
The way to do this is to use the Excel formula: VLOOKUP, and here is how. (You can also visit Microsoft’s official help page.)

First you’ll need to find a few things:

  1. The piece of information that needs to be matched with the reference list. In our example it is the place name, so “A2” for the first entry. This is what Excel calls lookup_value.
  2. The reference list where to look it up. In our example that will be the columns F and G, so “F:G”. Excel calls this table_array.
  3. The number of the column where the data to display is found. Here, it is the column “ZIP code reference”, which is G, the second column in the area (F:G) we just selected , so write “2”. For Excel, this is called col_index_num.
  4. Lastly, what Excel calls range_lookup, for which you will want to write “False” to perform an exact search in a list that is not ordered.

In the end, in the first cell to be filled (here, D2) we will write:   =VLOOKUP(A2,F:G,2,False)

In our example this displays “10013” the corresponding ZIP code.

Then just fill down, and the hard part is done.

This should do the trick. Check these extra tips if not.

Getting #N/A?
This may happen if the items in your reference list and those in your entries are not exactly the same. For example Excel won’t regognize “Factory one” or “Factory-1” if the reference list entry is “Factory 1”.

Check that the names are exactly the same.

Still not working?

Even when entries look precisely the same, they might not be. Sometimes – especially with data that has been manipulated, columns that have been split, etc – there are extra spaces after or before some text. This prevents Excel from recognizing it.

If you do find extra spaces after your entries, for example “Factory 1   “, but there are many entries to correct, use the Excel function “TRIM”.

Typing “=TRIM(A2)” for example, will remove all the spaces from the text in cell A2, aside from single spaces between words.

Getting #N/A when you fill-down?

If the first cell works but you get #N/A errors at some point further down, this might be because the reference area (Excel’s table_array) is not remaining constant. If you used a specific area as a table array (e.g. “F1:G7”) and not columns (e.g. “F:G”) it is likely that your array is being changed as you fill down.

Make sure you’ve used the $ sign before the values in your formula that should be constant when you fill down. In our example above, “A2” should change but if you want “F1:G7” to remain constant, write “$F$1$G$7”.


Once your data set has its geocoding information, upload it to Mapsdata, and your visualization is ready to be explored, displayed on your website with our great iframe feature, or shared with your colleagues and friends:

Now you should be ready to create your own data visualizations with Mapsdata.

Try now


 

If you’re still having problems creating your data visualization,  please check our How to use Mapsdata page. If you can’t find the solution to your problem, do send us a line on support@inquiron.com, we’re always happy to help.

 



Using VLOOKUP tutorial video:

Here is our YouTube video showing a different example of how to use VLOOKUP to add geocoding information to your data in order to create a visualization. We created it using the data from our visualization of the busiest London Underground stations.


 

If you’re still having problems creating your data visualization, please check our How to Use Mapsdata page. If you can’t find the solution to your problem, do send us a line on support@inquiron.com, we’re always happy to help.