Vlookup in excel with example step by step? Or about create a vlookup in excel? Check out vlookup in excel with example. I’ll share you how to do Vlookup in Excel along with example. The vlookup function is pretty useful if you need to quickly grab information from one table or list and put it into another table or list.
Have you ever wanted to create a vlookup in Excel? Maybe a few levels down a column? But you weren’t sure how to do it or maybe even heard that you shouldn’t? I’m going to show you the right way to do this.
The VLOOKUP function in Excel is a tool for looking up a piece of information in a table or data set and extracting some corresponding data/information. In simple terms, the VLOOKUP function says the following to Excel: “Look for this piece of information (e.g., bananas), in this data set (a table), and tell me some corresponding information about it (e.g., the price of bananas)”.
Learn how to do this step by step
Table of Contents
VLOOKUP Formula
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
To translate this to simple English, the formula is saying, “Look for this piece of information, in the following area, and give me some corresponding data from another column”.
The VLOOKUP function uses the following arguments:
- Lookup_value (required argument) – Lookup_value specifies the value that we want to look up in the first column of a table.
- Table_array (required argument) – The table array is the data array that is to be searched. The VLOOKUP function searches in the left-most column of this array.
- Col_index_num (required argument) – This is an integer, specifying the column number of the supplied table_array, that you want to return a value from.
- Range_lookup (optional argument) – This defines what this function should return in the event that it does not find an exact match to the lookup_value. The argument can be set to TRUE or FALSE, which means:
- TRUE – Approximate match, that is, if an exact match is not found, use the closest match below the lookup_value.
- FALSE – Exact match, that is, if an exact match not found, then it will return an error.
How to use VLOOKUP in Excel
Steps
- Open your Excel document. Double-click the Excel document that contains the data for which you want to use the VLOOKUP function.
- If you haven’t yet created your document, open Excel, click Blank workbook (Windows only), and enter your data by column.
- Make sure that your data is properly formatted. VLOOKUP only works with data that’s organized in columns (e.g., in vertical sections), meaning that your data most likely has headers in the top row of cells but not in the far-left column of cells.
- If your data is organized by rows instead, you’ll won’t be able to use VLOOKUP to find a value.
- Understand each aspect of the VLOOKUP formula. The VLOOKUP formula consists of four main parts, each of which refers to a piece of information in your spreadsheet:[1]
- Lookup Value – The cell across from which the data that you want to look up is in. For example, if you want to look up data in cell F3, your lookup value will be in the third row of the spreadsheet.
- Table Array – Your entire table’s span from the top-left cell to the bottom-right cell (not including headers). For example, if your table starts at A2, goes down to A20, and extends over to the F column, your table goes from A2 to F20.
- Column Index Number – The index number of the column in which the value that you want to look up is in. A column’s “index number” refers to its order number; for example, in a spreadsheet that has data in columns A, B, and C, the index number of A would be 1, B would be 2, and C would be 3. The index number starts at 1 for the far-left column of data, so if your data starts in column F, its index number is 1.
- Range Lookup – You’ll normally want a precise answer for the VLOOKUP result, which can be achieved by typing in FALSE for this value. For an estimate, you can type in TRUE instead.
- Select an empty cell. Click a cell in which you want to store the result of the VLOOKUP formula.
- Add the VLOOKUP formula tag. Type in =VLOOKUP( to start the VLOOKUP formula. The rest of the formula will go between the open parenthesis and a closed one at the end.
- Enter the lookup value. Find the cell in which the lookup value is written, then enter the cell’s name into the VLOOKUP formula followed by a comma.[2]
- For example, if the lookup value is written in cell A12, you’d type A12, into the formula.
- You’ll separate each section of the formula with a comma, but you don’t need to use spaces.
- Enter the table array value. Find the top-left cell in which data is stored and enter its name into the formula, type a colon (:), find the bottom-right cell in the data group and add it to the formula, and then type a comma.
- For example, if your table goes from cell A2 to cell C20, you’d type A2:C20, into the VLOOKUP formula.
- Enter the column index number. Find the index number of the column that contains the value that you want VLOOKUP to display, then type it into the formula with a comma after it.
- For example, if your table uses the A, B, and C columns and the data that you want is in C, you would type in 3, here.
- Type in FALSE) to close the formula. This will allow VLOOKUP to find the exact value in your specified column for the selected item. Your formula should look something like this:
- =VLOOKUP(A12,A2:C20,3,FALSE)
- Press ↵ Enter. Doing so will run your formula and display the result in the formula’s cell.
How To Find an Exact Match Using VLOOKUP?
VLOOKUP makes it effortless to look for an exact match from the table. Let’s take a look at how to do this with the help of an example:
- In the example below, we are using the VLOOKUP function to find the value of the exact match of ID from the given table. So, we set the first parameter as the lookup value, which is the cell H5.
- We specify the location of the table in the second argument. As you can see, the table location is A2:F11.
- The third argument specifies the Column Index number. This tells us what value should be returned from the row that we are looking up for. In the example, the product column is 3.
- The last argument is a Boolean Expression. Here, the value is set to FALSE for the VLOOKUP function to return an exact match for the value. An N/A error is displayed in case the exact value is not found.
With the help of VLOOKUP in Excel, we can look for an approximate match as well. You will learn this in the next section.
How To Find Approximate Match Using VLOOKUP?
Approximate Match works by finding the next largest value that is lesser than the lookup value, which we specify.
In the example below, we use the VLOOKUP function to find out how much RAM specification a laptop priced 1300 EUR has. Also, we know this value is not present in the table. So, let’s use the Approximate Match to find the solution. We need to sort the first column in ascending order. If not, VLOOKUP will return incorrect values.
- First, copy the price and RAM details to a new location, and specify your lookup value. Here, the lookup value is $1300.
Next, select your data range and click on the filter option to sort the values of the first column based on ascending order. After you click on the filter option, you will see the filter buttons enabled on your column headers.
- Filter the price details in ascending order. Click on OK.
- Now, enter your VLOOKUP formula. The first argument will be the VLOOKUP value. The second argument specifies the table range. In the third argument, we give the column number, so that values for that column are returned. Finally, the last argument is set to TRUE. This will allow the VLOOKUP function to find an approximate match for the value.
- After entering the formula, press enter. The value returned, in this case, is 8GB for the price of $1300.
Conclusion
Vlookup is a simple and efficient lookup formula of Excel which searches existing data and returns matching values from a specified table. It returns values from the topmost row of the table. The data matched by the vlookup function is supplied as an argument in the function. Moreover, vlookup is similar to Hlookup of MS Excel. Both the functions come with the same characteristic of looking for an exact match value.