How to use VLOOKUP in Excel

How to use VLOOKUP in Excel
(Image credit: Shutterstock)

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.

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.

How to use VLOOKUP in Excel step 1: Write lookup value then click an adjacent empty cell

(Image credit: Future)

2. In the formula bar, type “=VLOOKUP(“ without spaces.

How to use VLOOKUP in Excel step 2: Type "=VLOOKUP(" in the formula bar

(Image credit: Future)

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.

How to use VLOOKUP in Excel step 3: Click on the cell containing your lookup value

(Image credit: Future)

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.

How to use VLOOKUP in Excel step 4: Type a comma in the formula bar, then select the searchable range

(Image credit: Future)

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.

How to use VLOOKUP in Excel step 5: In the formula bar, type a comma followed by the number of the column you want to find data in. For example, if you want to look in the second highlighed column, type "2"

(Image credit: Future)

6. Type “,” immediately followed by “FALSE” to find an exact match within the range.

How to use VLOOKUP in Excel step 6: In the formula bar, type a comma followed by "FALSE"

(Image credit: Future)

7. Press the Enter key on your keyboard, and the return value data will appear in the cell you selected during step 1.

How to use VLOOKUP in Excel step 7: Press the Enter key

(Image credit: Future)

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.

James Archer

James is currently Hardware Editor at Rock Paper Shotgun, but before that was Audio Editor at Tom’s Guide, where he covered headphones, speakers, soundbars and anything else that intentionally makes noise. A PC enthusiast, he also wrote computing and gaming news for TG, usually relating to how hard it is to find graphics card stock.