Posted on 3 How To

VLOOKUP formula: what is it, how to use VLOOKUP in Microsoft Excel with an example

Microsoft Excel or MS Excel is one of the most powerful tools when it comes to managing, filtering and exploring data. The spreadsheet software developed by Microsoft is regularly used by millions of users on Mac, Windows, Android and iOS. MS Excel offers computing capabilities, graphics tools, visualization tools, and more.

With features like formula and functions, Excel can handle anything you throw at it in terms of calculations. Things get more interesting when you start using formulas, and one such formula in the MS Excel platform is the VLOOKUP formula.

In this article, we will look at what the VLOOKUP formula is and why it is used. We will also look at how the formula can be used with some examples and syntax.

What is the VLOOKUP formula in Excel and why is it used?

VLOOKUP is a quite handy and powerful formula in MS Excel and also one of the most widely used formulas to save time and effort. First, VLOOKUP stands for vertical search. This formula is used when you need to find something in a table or range by row. It looks for the key in the first column of the range and returns the value of the specified cell in the found row.

Here is a simple form for using the VLOOKUP formula:

=VLOOKUP(What you want to find, where you want to find it, the column number in the range containing the return value, returns an approximate or exact match – specified as 1/TRUE or 0/FALSE)

Examples:

  • =VLOOKUP(1003, A2:B26, 2, FALSE)
  • =VLOOKUP(A4,A10:C20,2,TRUE)
  • =VLOOKUP(“Steve”,B1:E7,2,FALSE)
  • =VLOOKUP(A2,’Customer Details’!A:F,3,FALSE)

How to use VLOOKUP formula in Excel

Now that you have seen some of the examples above, here is the syntax for using the VLOOKUP formula in Excel.

Syntax: VLOOKUP(lookup_value, table_array, column_index_number, [view_range])

Let’s see what the arguments in brackets mean, whether they are required and what value you need to fill in when using it.

  • lookup_value (required)

Here you need to enter the value you want to search/find. The value you want to find must be in the first column of the range of cells you specify in the table_array argument. It can be a value or a cell reference.

Example: if the table array spans cells B2:D7, then lookup_value must be in column B.

  • table_array (required)

The second required field you need to enter, table_array refers to the range of cells in which the VLOOKUP function will look up lookup_value and then return the value. You can use a named range or table, and names in arguments instead of cell references. The first column in the cell range must contain the lookup_value. The cell range must also include the return value you want to find.

  • col_index_num (required)

The number of the column that contains the return value. The number starts at 1 for the leftmost column of table_array.

  • range_lookup (optional)

This optional field accepts a Boolean value indicating whether you want the VLOOKUP function to find an approximate or exact match. You can enter 1/TRUE to find an approximate match. This assumes that the first column in the table is sorted either numerically or alphabetically, and then the closest value will be searched. This is also the default method if you don’t enter anything manually.

Example: VLOOKUP(90,A1:B100,2,TRUE)

To get an exact match you can use 0/FALSE which looks for the exact value in the first column.

Example: =VLOOKUP(“Smith”,A1:B100,2,FALSE)

Pro Tip: To build the VLOOKUP syntax faster, write “=vl”and then hit the tab key to autocomplete the formula name.

Now that we understand how and when to use the VLOOKUP formula, let’s look at a real life example.

  • Open the data spreadsheet where you want to use the VLOOKUP formula.
  • Click on the cell where you want to execute the VLOOKUP formula
  • Enter =VLOOKUP(
  • Enter the search value you want to get
  • Enter the range of cells in which the VLOOKUP function will look for the entered value.
  • Then enter col_index_num where we want to find the lookup value and return value.
  • Finally, enter 1/TRUE for an approximate match, or 0/FALSE for an exact match.
  • Press Enter and you will get a result containing the value you were looking for in the previous step.
  • Google Docs Shortcuts: 50 Best Google Docs Keyboard Shortcuts for Windows PC and macOS Laptop
  • How to disable Windows Defender Antivirus on Windows 10 PC or laptop?
  • Disable Windows Updates: How to Stop Automatic Updates in Windows 11 and Windows 10

CDN CTB