How to Use Vlookup in Excel Spreadsheet

This wikiHow teaches you how to find the corresponding information of a cell in Microsoft Excel using the VLOOKUP formula. The VLOOKUP formula is very useful in finding information such as employee salary value or budget for a given day. You can use VLOOKUP on both Windows and Mac versions of Excel.

Table of Contents

Steps

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/3/30/Use-Vlookup-With-an-Excel-Spreadsheet-Step-1-Version-5.jpg/v4-728px- Use-Vlookup-With-an-Excel-Spreadsheet-Step-1-Version-5.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/3/30/Use-Vlookup- With-an-Excel-Spreadsheet-Step-1-Version-5.jpg/v4-728px-Use-Vlookup-With-an-Excel-Spreadsheet-Step-1-Version-5.jpg”,”smallWidth”:460 ,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}

Open the Excel document. Double-click the Excel document containing the data for which you want to use the VLOOKUP feature.

If you haven’t created the document yet, open Excel, click Blank workbook (Windows only) and enter the data in columns.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/9/98/Use-Vlookup-With-an-Excel-Spreadsheet-Step-2-Version-5.jpg/v4-728px- Use-Vlookup-With-an-Excel-Spreadsheet-Step-2-Version-5.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/9/98/Use-Vlookup- With-an-Excel-Spreadsheet-Step-2-Version-5.jpg/v4-728px-Use-Vlookup-With-an-Excel-Spreadsheet-Step-2-Version-5.jpg”,”smallWidth”:460 ,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}

You need to make sure that the data is properly formatted. VLOOKUP only works with data that is organized in columns (e.g. vertical sections), which means that the data most likely has headings in the top row and not the cells of the leftmost column.

If the data is sorted in rows, you won’t be able to use VLOOKUP to find the value.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/0/03/Use-Vlookup-With-an-Excel-Spreadsheet-Step-3-Version-5.jpg/v4-728px- Use-Vlookup-With-an-Excel-Spreadsheet-Step-3-Version-5.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/0/03/Use-Vlookup- With-an-Excel-Spreadsheet-Step-3-Version-5.jpg/v4-728px-Use-Vlookup-With-an-Excel-Spreadsheet-Step-3-Version-5.jpg”,”smallWidth”:460 ,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}

We need to understand each aspect of the VLOOKUP formula. The VLOOKUP formula consists of four parts, each of which reflects one aspect of the information in the spreadsheet: ^{[1] X Research Source}

Lookup Value – The cell that contains the data you want to look up. For example, if you want to find data in cell F3 , the lookup value will be in the third row in the spreadsheet.
Table Array – The entire data range of the table from the top left cell to the bottom right cell (excluding headers). For example, if the data starts from A2 down to A20 and extends to the end of column F , the table array will range from cell A2 to cell F20 .
Column Index Number – The column index number containing the value you want to look up. The “index number” column represents the order of the column; for example, in a spreadsheet where the data is in columns A , B and C , the index number of column A will be 1, B is 2 and C is 3. The index number starts at 1 and counts from the data column on the side. outer left, so if the data starts from column F then the index number of this column will be 1.
Range Lookup – Usually we all want the correct answer for VLOOKUP results, so enter FALSE for this value. If you just want an estimate, you can enter the value TRUE .

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/f/f5/Use-Vlookup-With-an-Excel-Spreadsheet-Step-4-Version-4.jpg/v4-728px- Use-Vlookup-With-an-Excel-Spreadsheet-Step-4-Version-4.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/f/f5/Use-Vlookup- With-an-Excel-Spreadsheet-Step-4-Version-4.jpg/v4-728px-Use-Vlookup-With-an-Excel-Spreadsheet-Step-4-Version-4.jpg”,”smallWidth”:460 ,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}

Select a blank cell. Click the cell where you want to display the result of the VLOOKUP formula.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/4/41/Use-Vlookup-With-an-Excel-Spreadsheet-Step-5-Version-4.jpg/v4-728px- Use-Vlookup-With-an-Excel-Spreadsheet-Step-5-Version-4.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/4/41/Use-Vlookup- With-an-Excel-Spreadsheet-Step-5-Version-4.jpg/v4-728px-Use-Vlookup-With-an-Excel-Spreadsheet-Step-5-Version-4.jpg”,”smallWidth”:460 ,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}

Add VLOOKUP recipe tag. Type =VLOOKUP( to start the VLOOKUP formula.The rest of the formula will be between the opening parenthesis and the closing parenthesis at the end.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/8/80/Use-Vlookup-With-an-Excel-Spreadsheet-Step-6-Version-4.jpg/v4-728px- Use-Vlookup-With-an-Excel-Spreadsheet-Step-6-Version-4.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/8/80/Use-Vlookup- With-an-Excel-Spreadsheet-Step-6-Version-4.jpg/v4-728px-Use-Vlookup-With-an-Excel-Spreadsheet-Step-6-Version-4.jpg”,”smallWidth”:460 ,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}

Enter the search value. Find the cell where the lookup value is written, and then enter the cell name into the VLOOKUP formula along with a comma. ^{[2] X Research Source}

For example, if the lookup value was entered in cell A12 , you would enter A12, in the formula.
You need to separate each part of the formula with a comma, but don’t use spaces.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/4/41/Use-Vlookup-With-an-Excel-Spreadsheet-Step-7-Version-4.jpg/v4-728px- Use-Vlookup-With-an-Excel-Spreadsheet-Step-7-Version-4.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/4/41/Use-Vlookup- With-an-Excel-Spreadsheet-Step-7-Version-4.jpg/v4-728px-Use-Vlookup-With-an-Excel-Spreadsheet-Step-7-Version-4.jpg”,”smallWidth”:460 ,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}

Enter the table array value. Locate the upper left cell containing the data to enter the name of the cell in the formula, enter a colon ( : ), find the lower right cell in the data group, add the formula, and finally a comma.

For example, if the table goes from cell A2 to cell C20 , you would enter A2:C20, in the VLOOKUP formula.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/7/79/Use-Vlookup-With-an-Excel-Spreadsheet-Step-8-Version-4.jpg/v4-728px- Use-Vlookup-With-an-Excel-Spreadsheet-Step-8-Version-4.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/7/79/Use-Vlookup- With-an-Excel-Spreadsheet-Step-8-Version-4.jpg/v4-728px-Use-Vlookup-With-an-Excel-Spreadsheet-Step-8-Version-4.jpg”,”smallWidth”:460 ,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}

Enter the column index number. Specify the index number of the column containing the value you want VLOOKUP to display, then enter the formula with a comma after it.

For example, if the table uses columns A , B and C , where the data you want is in column C , enter 3, here.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/2/24/Use-Vlookup-With-an-Excel-Spreadsheet-Step-9-Version-4.jpg/v4-728px- Use-Vlookup-With-an-Excel-Spreadsheet-Step-9-Version-4.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/2/24/Use-Vlookup- With-an-Excel-Spreadsheet-Step-9-Version-4.jpg/v4-728px-Use-Vlookup-With-an-Excel-Spreadsheet-Step-9-Version-4.jpg”,”smallWidth”:460 ,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}

Type FALSE) to close the formula. This information allows VLOOKUP to find the correct value in the specified column for the item you selected. The formula should now look similar to this:

=VLOOKUP(A12,A2:C20,3,FALSE)

Advice

A common application of VLOOKUP in an inventory table is to include the item name in the “lookup value” section and use the item’s cost column as the “column index number” value.
To keep the cell value unchanged in the VLOOKUP code when you add or edit cells in the table, add a ‘$’ in front of each letter and number of the cell name. For example, A12 would be $A$12 , and A2:C20 would be $A$2:$C$20 .

Warning

Double-check the column index number before entering the VLOOKUP formula because the column index number will vary depending on the column in which the data begins, not the column order itself.

Steps

Advice

Warning

Related Posts