How to use the VLOOKUP function in Microsoft Excel to find related data points in your worksheet

Advertisement
How to use the VLOOKUP function in Microsoft Excel to find related data points in your worksheet
The VLOOKUP formula in Excel is great for finding relationships between different data points.justplay1412/Shutterstock
  • You can use the VLOOKUP function in Microsoft Excel to write a simple formula that will find specific data points you're looking for.
  • To use VLOOKUP in Excel, open the "Formulas" menu at the top of the screen.
  • The VLOOKUP function is most helpful when you're trying to find a specific value in a large spreadsheet.
  • The "V" in VLOOKUP stands for vertical, meaning Excel will scan for data points in columns instead of rows.
Advertisement

The VLOOKUP function in Microsoft Excel is a great way to find relational data.

For example, you have a busy worksheet with a lot of data points, and you want to know the name of a supplier and the corresponding supplier's specific phone number, VLOOKUP can come in handy.

There are four necessary pieces of information that you need to know first before you use the VLOOKUP function — including the lookup value, the range of where the value is located, the column number (not letter) it's in, and whether you want an exact or approximate match.

Complimentary Tech Event
Transform talent with learning that works
Capability development is critical for businesses who want to push the envelope of innovation.Discover how business leaders are strategizing around building talent capabilities and empowering employee transformation.Know More

It seems like a lot, but once you get a handle on the function, it'll become much easier.

Check out the products mentioned in this article:

Microsoft Office Home (From $149.99 at Microsoft)

Apple Macbook Pro (From $1,299.00 at Apple)

How to use VLOOKUP in Excel

1. Open Microsoft Excel.

Advertisement

2. Open a saved worksheet, or enter your data into a new worksheet before continuing.

3. Select the cell where you want to execute your VLOOKUP formula. It should be blank, so your VLOOKUP result doesn't overwrite anything. In this example, we'll use cell C4.

4. At the menu at the top, select "Formulas."

How to use the VLOOKUP function in Microsoft Excel to find related data points in your worksheet
Select "Formulas" from the top menu.Meira Gebel/Business Insider

5. Select "Lookup & Reference."

How to use the VLOOKUP function in Microsoft Excel to find related data points in your worksheet
Select "Lookup & Reference."Meira Gebel/Business Insider

6. Scroll down and select VLOOKUP from the dropdown menu.

Advertisement
How to use the VLOOKUP function in Microsoft Excel to find related data points in your worksheet
Select "VLOOKUP" from the bottom of the drop-down menu.Meira Gebel/Business Insider

7. A pop-up screen will then appear on the right where you'll enter your data fields. First, enter the value you're looking for. It can be anything from a name, number, or cell location. In this example, we'll look for pop-singer Lizzo's Instagram followers — so we would enter "Lizzo" into the text box. The function is case sensitive, so enter the data carefully.

How to use the VLOOKUP function in Microsoft Excel to find related data points in your worksheet
Enter the fields into the formula builder on the right.Meira Gebel/Business Insider

8. Now you will enter where Excel should search for the data — this can be two or more columns, creating a range on the lookup table. Remember: VLOOKUP searches for data vertically, so be sure to enter the column letter followed by the row number. For this example, we want to search through all the data, encompassing all fields on the worksheet, so we'll enter "A2:B16."

9. Next, Excel wants you to enter the "column number in the range that contains the return value" — this data point should be where you want the data to come from. In our example, Instagram followers are represented in column B, so we would enter the numerical value of B, which is two (it's the second column).

10. Finally, enter whether you want the results to be approximate (TRUE) or exact (FALSE). We want our results to be spot on so we will write FALSE. All together our formula looks like this: =VLOOKUP("Lizzo",A2:B16,2,FALSE).

11. Select "Done" at the bottom of the formula window.

Advertisement
How to use the VLOOKUP function in Microsoft Excel to find related data points in your worksheet
Select "Done" once you've filled all necessary fields. The result will then appear in the cell you selected.Meira Gebel/Business Insider

12. The value will then appear in the cell you selected at the beginning. From our example, we now know Lizzo has 6.4 million Instagram followers without having to manually search the list for it.

Related coverage from How To Do Everything: Tech:

{{}}