March 2017 Excel Tip of the Month - VLOOKUP Formula
Presented by Aaron Cooper, Partner, Brown Smith Wallace
Have you ever had trouble quickly locating a specific value in your large data sheet? VLOOKUP is the perfect solution. VLOOKUP is one of the most useful functions in Microsoft Excel, and easier to learn than you think!
The “V” in VLOOKUP stands for vertical. This means it is used to search values inside a column instead of a row. HLOOKUP stands for “horizontal” and is used to search values in rows. However, data is almost always listed in columns, so we are going to focus on VLOOKUP.
Here are the 3 pieces of information you need in order to use VLOOKUP:
- The value you want to look up (LOOKUP_VALUE)
- The table of data you want the information retrieved from (TABLE_ARRAY)
- The column containing information you want to bring back (COL-INDEX-NUM)
- Whether you want close but not exact matches returned (RANGE_LOOKUP)
Once you have this information identified, here is how to use VLOOKUP:
- Select the cell that you would like to search
- Select the column where you want the information retrieved from
- Select the “Formulas” tab from the top toolbar, and select “Insert Function” located on the left side
- A list of function options will appear, click “VLOOKUP” from the list
- The “Function Arguments” box will appear. This is where you enter the 4 pieces of information I mentioned earlier (LOOKUP_VALUE, TABLE_ARRAY, and COL-INDEX-NUM, RANGE_LOOKUP)
To learn more about tips and tricks for Excel, check out these additional resources:
1. Tips and Tricks - Excel and Windows
2. Excel Series - Pivot Tables (The Basics)
3. Excel Series - Pivot Tables (Beyond the Basics)
4. Excel Series - Tools & Techniques for Detecting and Preventing Spreadsheet Errors
Aaron is a partner in the Brown Smith Wallace Insurance Advisory Services practice. He is a regular content contributor to IASA national and the St. Louis chapter. You can reach Aaron at firstname.lastname@example.org.