How to Automatically Fill Out Forms using Data Validation and VLOOKUP

Manifest LeadThe business world is full of forms and paperwork built out of Excel spreadsheets. Even though the program isn’t designed for the task, the benefit of being able to calculate sums, totals, etc. in the form makes it the de facto standard for invoices, receipts, shipping manifests, and purchase orders everywhere. Often, you’ll find yourself using the same forms over and over again, so it makes sense to automate as much of the process of filling them out as possible. I’ll show you how to automatically fill addresses and contact information into a template shipping manifest…

Examining the Form and Data

First, let’s look at our existing form. It has standard fields for Company Name, Address, City, State, ZIP Code, Phone Number, and Contact Name. All of them have to be manually entered right now.

Shipping Manifest

Then, let’s examine the data. The information we need is all there, but it is saved in plain list, which makes it very difficult to search through, sort, and find things in.

Shipper Addresses

Cleaning Up (Scrubbing) the Address Data

In order to work with the address data, we need to re-format it into more predictable columns and rows. Ideally, each entry should have it’s own row, with a column devoted to each data point. Since there is a manageable number of entries in our example, it is probably easiest to manually repair the data set. Were this a longer list, it may be worth crafting a formula or two to pull out the information. The result of our data scrubbing looks like this:

Scrubbed Addresses

Creating a Drop-Down List Box

Now the the data is cleaned up and organized, we can start to work with it in Excel. First, we are going to create a drop-down box under the Company Name field in the Shipping Manifest. This will make it easy to choose which shipping information we need. When new shippers are added, they can be added to the new data table and they will automatically be added to the list.

Select the data entry cell for Company next to Ship to:

Select Company Field

Under the Data menu tab, choose Data Validation and then click on Data Validation.

Data Validation

In the Data Validation dialog box that appears, under Allow section, choose List. Then click the data field next to Source.

Switch to the Shipping Addresses tab and select column A. Then click OK.

Select List

If you go back to the Manifest sheet, you can look at the Ship to: data field and find the drop down box. It will show you your available options.

Drop Down Box

Automatically Filling the Data Fields

With the drop down box complete, we need to start linking the data fields to the output. Select the data entry cell for Address under the company name (F5 in the example).

To find the correct address information, we’re going to use an Excel function called VLOOKUP(). The syntax for VLOOKUP is as follows:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

The lookup_value is what we’re looking for in the table. In this case, that’s the company name, which is in cell F4.

The table_array is the set of rows and columns that we want VLOOKUP to scan for a match. The first column must have the lookup_value in it. In our case, the value is the columns of the Shipper Address tab.

The col_index_num is the column of the table_array we actually want to appear in the cell. In this case, it is the address column, which is the second column (column B) of our table_array.

Finally, range_lookup tells VLOOKUP whether we are okay with close matches instead of exact. We need an exact match, so the value will be FALSE.

If VLOOKUP doesn’t find a match in the table, it throws an error, which isn’t good if we need to enter an address that isn’t on the Shipper Address list. To handle the error and keep the form clean, we’ll wrap the VLOOKUP in another function called IFERROR(). The syntax for IFERROR is as follows:

=IFERROR(value, value_if_error)

The value will be the VLOOKUP function.

The value_if_error will just be an empty string (“”) so the form stays blank when there is no company selected.

The final formula for the Address field is as follows:

=IFERROR(VLOOKUP($F$4,'Shipper Addresses (Formatted)'!A:G,2,FALSE),"")

The process will repeat for Contact Name and Phone Number. The same formula can be used, but the col_index_num will change accordingly.

The formula for Contact Name will be in cell F9 and is as follows:

=IFERROR(VLOOKUP($F$4,'Shipper Addresses (Formatted)'!A:G,6,FALSE),"")

The formula for Phone Number will be in cell F9 and is as follows:

=IFERROR(VLOOKUP($F$4,'Shipper Addresses (Formatted)'!A:G,7,FALSE),"")

Automatically Filling City, State, and Zip Code

The City, State, and ZIP Code data are held in separate cells, so we’ll need to stitch them together using multiple VLOOKUP functions. Otherwise, the process is the same. To stitch them together, we can use the CONCATENATE() function, which turns multiple strings into one long string. The syntax for CONCATENATE is as follows:

=CONCATENATE(text1, [text2], ...)

For example, =CONCATENATE(“to”,”get”,”her”) calculates to “together”.

The formula for City is as follows:

=IFERROR(VLOOKUP($F$4,'Shipper Addresses (Formatted)'!A:G,3,FALSE),"")

The formula for State is as follows:

=IFERROR(VLOOKUP($F$4,'Shipper Addresses (Formatted)'!A:G,4,FALSE),"")

The formula for ZIP Code is as follows:

=IFERROR(VLOOKUP($F$4,'Shipper Addresses (Formatted)'!A:G,5,FALSE),"")

We’ll need to stitch them together with CONCATENATE and add the proper commas and spaces. The final formula for the City, State ZIP data field is in cell F6 and reads as follows (on one line):

=CONCATENATE(IFERROR(VLOOKUP($F$4,'Shipper Addresses (Formatted)'!A:G,
3,FALSE),""),", ",IFERROR(VLOOKUP($F$4,'Shipper Addresses (Formatted)'!A:G,
4,FALSE),"")," ",IFERROR(VLOOKUP($F$4,
'Shipper Addresses (Formatted)'!A:G,5,FALSE),""))

Automatically Filled Shipping Manifest Example

That’s it! Your drop-down box now automatically fills in the Address, Contact Name, and Phone Number on the Manifest! You can play around with the example before it was scrubbed and automated on the first two worksheets and after on the second two spreadsheets. You can also download the sample file by clicking the Excel icon in the bottom right.

Get the latest Excel tips and tricks by joining the newsletter!

Andrew Roberts ThumbnailAndrew Roberts has been solving business problems with Microsoft Excel for over a decade. Excel Tactics is dedicated to helping you master it. You can read more of his writing on his personal blog at NapkinMath.io.

Join the newsletter to stay on top of the latest articles. Sign up and you'll get a free guide with 10 time-saving keyboard shortcuts!

14 thoughts on “How to Automatically Fill Out Forms using Data Validation and VLOOKUP

  1. Hey,
    I want to have this worked out for multiple receipts say 300+, so for every entry I’d have to run this formula on each cell?

  2. This was great! However, what I really need is an understanding of how to work the body of the manifest. I’m creating a quote that looks very similar and has 6 columns. I would like to have a dropdown for each cell in column 1 for “type.” Then, the 2nd column would only display the PKG Types available for the “type” selected in column 1. The 3rd would be “product” and again, I would only want the cells in column 3 to dropdown the products available based on the first 2 selections. Then the cells in the 4th column would only display the different case sizes based on the first 3 selections. The last 2 columns would auto-populate (pkg price and case /pallet price) based on the first 4 selections. I’ve spent hours trying to figure this out and have no idea. This is for Excel 2016 for Mac.

    1. you would place a =indirect(VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) in the second drop down instead of vlookup() on its own. indirect selects the value based on the selection.

  3. I’m creating databases of old, collectible magazines and their contents. The autofill feature kicks in when I’m typing directly into the cell. When I select Data>Form from the toolbar menu, data doesn’t autofill the form. Am I doing something wrong, or is this not an option?

  4. When I add a new “shipper” to the second sheet then go back to the drop down we created the name is there but the info doesn’t fill in without me going to each cell where info to autopopulate is and hitting enter at the end of the formula? I downloaded your spreadsheet and yours worked no problem. Any thoughts as to what I haven’t done or didn’t do correctly? Thank you.

  5. How do i autocomplete the body of the document with data in more than one row in another spread sheet? Thank you for your assistance. It is extremely helpful. I have a large spreadsheet and want to only include certain information that is more reader friendly for directors on respective files. How can i, instead of using a drop down list type in the main cell and the program automatically select the information needed, when typing, from another spreadsheet?

  6. Hi Andrew,

    I came on your nice clear explanation of this very useful technique today looking to go a bit further with it.

    Using your example, but imagining you had 4000 company names, not just 4, how can I modify the formula(s) to filter the contents of the company name dropdown list such that they match, say, the first 2 or 3 letters I type into it (of the lookup value)?

    I’m just trying to make the dropdown list much shorter so I don’t have to scroll around looking for the precise item amongst lots of simililar items.

    Thanks. Martin

  7. Martin, Is there a way to have it so when I type new information on the Sheet 2/ Shipper Addresses it will auto fill sheet 1/Manfest or vice vera?

  8. I can’t get your formula to work for me; strangely, I am not getting errors… and it LOOKS exactly right… but it continues to populate nothing at all when I change the field that VLOOKUP references. If I take out the IFERROR, it just populates “#N/A” in the cell where the data should be populating. I’m using Office 365.
    I’m attempting to use this to autofill rent receipts (for some reason nobody has invented this???). I have a Master Rent Log in the first tab, and the receipt on the second tab. I chose to make the RECEIPT # the VLOOKUP item/column because it will never be the same number. Any idea why my formula refuses to populate the actual information??

  9. I cannot seem to get the last part (the important part) to work. I knew how to set up the Data Validation and dropdown list, as I’ve done that many times before. It’s the Linking that isn’t working — as far as I can tell I’ve set it up correctly; yet clearly not, because when I choose/enter an item from my dropdown list, nothing populates in the cell with the formula. I am attempting to simplify my Excel rent receipts which I normally have to type out one-by-one, which is tedious… I am a superintendent of 50 units and have other things to do.
    Since each receipt gets a unique number that won’t ever be repeated, I decided that would be the best field to use for my VLOOKUP reference.
    I already have my Master Rent Log as a Table in the first tab, aptly named “master rent log” — so my receipt is on a second tab… called “receipt”
    SO… my Data Validation dropdown list is in I1 (sorry, it’s column “i” lol).
    When I select or type in the receipt number there, I want it to then auto-populate the data from the other columns into their appropriate cells;
    Date Paid, Tenant Name, Amount, Type (chq/cash/etc), Item # (chq#), etc.
    My formula reads:
    =IFERROR(VLOOKUP($I$1,’master rent log’!A:K,6,FALSE),””)
    ….and nothing happens.
    lol…
    I’ve also tried it with just the table name ‘Table1’ as that’s what the cell defaults to when I SELECT the table itself instead of typing it in… same result.
    I tried without the IFERROR part, to see what happened but it just populates “#N/A” in the cells instead.

    Any advice?

Leave a Reply

Your email address will not be published. Required fields are marked *