Save content
Have you found this content useful? Use the button above to save it to your profile.
AIA

Excel tip: Boost data integrity with VLOOKUP

by
6th May 2014
Save content
Have you found this content useful? Use the button above to save it to your profile.

David Ringstrom says that building complex spreadsheets without using VLOOKUP is like putting a screw in the wall with a hammer. It’s possible, but the results aren’t pretty.

Relying on manual references and links probably is not an approach designed to maintain the integrity of your spreadsheets. The same is true for sheets where you manually reference individual cells over and over again, rather than letting Excel do the work for you.

Take the example of looking up an addresses from a list based on a name. People who aren’t used to using VLOOKUP will often copy and paste the information manually, or creating a formula that points to the information.

If the reference is for a commonly used form such as an invoice, manual lookups can become tedious.

Excel’s VLOOKUP function can automate this process, and ensure much more reliable results, simply by feeding it four pieces of information:

  1. Lookup value - This would be reference you want to match in your data - in this case the name "Ted Smith" that has been typed into cell A1 in the illustration below.
  2. Table array - These are based on the coordinates of our name/address list. To work properly, the lookup value must appear within the first column of the table array to which the VLOOKUP argument refers.
  3. Column index number - This will typically be 2 or greater, and signifies the column position within the table array from which information will be returned. In the address list in this example, the table array spans columns C through E. We’ll use the number 2 to signify that we want to return the street address from the second column of the table array.
  4. Range lookup - The range lookup argument can have a big effect on how VLOOKUP works. I generally put the word FALSE in this position, which signifies that I want an exact match. If I were to use the word TRUE, then VLOOKUP would return data based on an approximate match. Where you are looking up an address, as in this example, you will almost always want exact matches.

Bearing these instructions in mind, here’s how to use VLOOKUP to return the address for Ted Smith, whose name was entered in cell A1. The next step is to enter the following formula in cell A2:

=VLOOKUP(A1,C1:E6,2,FALSE)

Use VLOOKUP to return data from a list based on criteria that you specify.

Using our breakdown of VLOOKUP’s terminology, A1 is the lookup value, C1:E6 is the table array, 2 is the column index number, and FALSE signifies that we’re looking for an exact match. Once the formula is in place, type Jane Seyz in cell A1, and you should see the address change automatically.

The formula to return the City/State/Zip (or County/Postcode for UK readers) will be almost the same as the street address: just specify a column index number of 3 instead of 2. You can also use another of my favourite VLOOKUP tricks, which is to insert a zero in place of the word FALSE to signify an exact match:

 =VLOOKUP(A1,C1:E6,3,0)

Use a zero in place of the word FALSE to specify an exact match with VLOOKUP.

If you type a name that isn’t on the list, such as Vera Floyd, VLOOKUP will return #N/A. This indicates the data you entered does not appear in column C of the list.

High Impact Excel: VLOOKUP webinar

This tip is just a brief introduction to VLOOKUP. To learn more about lookup formulas, check out David’s High Impact Excel: VLOOKUP webinar, available to view for free from our sister site, AccountingWEB.com

Tags:

Replies (0)

Please login or register to join the discussion.

There are currently no replies, be the first to post a reply.