Knowing how to use VLOOKUP in Excel can be extremely useful when you’re dealing with huge tables. It’s not just like using Ctrl+F to search for a specific word or number: VLOOKUP searches a specific, user-defined range, and returns data associated with the lookup term as opposed to the term itself.
Say you need to find the price for a certain item, or want to find out which colleague is working on a specific project. As long as it’s organized by row, like any self-respecting Excel table should be, VLOOKUP can find the data you need.
- See how to delete a page in Microsoft Word
- Here’s how to schedule Slack messages
- How to rotate the screen in Windows 10
Using VLOOKUP does require punching in a formula, but doesn’t demand expert algebraic knowledge. To demonstrate how it works as simply as possible, we’ll walk you through an example where we use VLOOKUP to find out a person’s working hours; as you’ll see in the images, it’s only a small table, but this process works (and is intended) for much bigger tables and ranges.
There are also a couple of terms to get familiar with. “Lookup value” is, effectively, our search term: the word, phrase or data that we’ll have VLOOKUP search for. “Return value” is like a search result: it’s the data that VLOOKUP will fetch and present, having found it sorted with the lookup value by row. For VLOOKUP to work, the column containing the lookup value should be to the left of the column containing the return value, so shuffle around your table if they're not in the optimal order.
How to use VLOOKUP in Excel
1. Write the lookup value in one cell, then click on an empty cell adjacent to it.
2. In the formula bar, type “=VLOOKUP(“ without spaces.
3. Click on the cell containing the lookup value you entered. Again, this should be to the left of the empty cell you originally clicked. Note how the cell number — in our example, A10 — now appears in the formula bar.
4. In the formula bar, type “,” then click and drag to select the cells you want to search through. This creates the “range” within which VLOOKUP will find the data you want: the return value.
5. This is where it gets a bit tricky, since the process involves breaking with how Excel normally identifies columns with letters rather than numbers. Here, you need to mentally assign numbers to each column you have highlighted in your range: in our example, we’ve highlighted the A and B columns, and will number then as 1 and 2 respectively. If, hypothetically, you were to highlight the C, D and E columns, you'd number them 1, 2 and 3 respectively.
With the range highlighted, continue typing in the formula bar. This time, enter “,” followed by the “number” of the column that’s likely to contain the return value. For our example, although we’re looking for a name that matches one in column A, what we’re really looking for is the data in column B, so we’re going to type “2” after the comma.
6. Type “,” immediately followed by “FALSE” to find an exact match within the range.
7. Press the Enter key on your keyboard, and the return value data will appear in the cell you selected during step 1.
This covers how to use VLOOKUP on a basic level; just remember to keep your data organized so that the more easily memorable lookup value is to the left of the return value.
For more on getting the most out of Microsoft Office apps, check out our guides on how to how to sign a Word document, how to convert a PDF to Excel and how to save a Word document as a PDF.
- More: The best laptops we've tested
