A Beginner’s Guide to Using VLOOKUP in Excel

When it comes to finding specific information in large or complicated spreadsheets, VLOOKUP is the go-to tool. It’s like a search function that, together with HLOOKUP, can locate values across the entire sheet. Trust me, this tool is incredibly useful and something every Excel user should know how to use. Let me show you the ropes on using VLOOKUP in Excel 2016.

VLOOKUP is not just your regular Ctrl + F function in other Office documents. It’s a powerful vertical (hence, the ‘V’) lookup tool that can search the first column of a sheet for any item you’re looking for. When paired with HLOOKUP, which is the horizontal lookup tool, you can effortlessly search and find what you need in no time.

Discovering VLOOKUP in Excel 2016

When you want to use VLOOKUP in Excel 2016, there are three important things you need to know. First, you need to know the value you’re searching for. Second, you’ll need the data that you want to search. And finally, you’ll need to know the column where you want to place the results. These three pieces of information are expressed as lookup_value, table_array, col_index_num, and range_lookup. Alternatively, you can use this formula:

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

• lookup_value is simply what you’re looking for.
• table_array refers to the range of cells where you want to search.
• col_index_num represents the data you want to search for.
• range_lookup determines whether you want an exact match or an approximate match.

How to Use VLOOKUP in Excel

Now that you know what VLOOKUP is, let’s dive into how to use it. I’ll walk you through the process step by step, making it easy for you to understand.

You might notice some ‘$’ signs scattered within the formula. These tell Excel that certain terms are absolute and shouldn’t be changed. It might make the formula harder to read, but trust me, it’ll make your life a whole lot easier.

The final formula we’ll end up with is ‘=VLOOKUP($F$2,$A$2:$C$55,3,FALSE)’

Here’s how you can build it:

1. Open your spreadsheet and find a place to display the results. Put a box around it or mark it in some way so that it’s clear what will be returned (let’s say we’re using Cell F3 in this example).
2. Add a box above it to input your search criteria (let’s say we’re using Cell F2 in this example).
3. Type ‘=VLOOKUP(‘ into Cell F3, where you want the data to be displayed.
4. Add the location where you want the data to appear. For example, if you want the result to be in Cell F2, your formula should look like: ‘=VLOOKUP($F$2’.
5. Include the search range of the spreadsheet. In this example, it would be cells A2 to C55. ‘=VLOOKUP($F$2,$A$2:$C$55’.
6. Specify what value you’re looking for. In our case, it’s the salary, so we’ll use column 3. ‘=VLOOKUP($F$2,$A$2:$C$55,3’.
7. Decide if you need an exact match or an approximate one by using TRUE or FALSE. We want an exact match, so: ‘=VLOOKUP($F$2,$A$2:$C$55,3,FALSE’.
8. Press Enter.
9. Type a search criteria into the box at F2 that you created in step 2 and see the result in the box below at F3.

And there you have it, the anatomy of using VLOOKUP in Excel 2016. It’s a powerful tool that can significantly simplify working with larger spreadsheets.

Special thanks to Spreadsheeto.com for providing the sample.