Vlookup in Excel Step by Step
By Excel is a powerful tool that is widely used in businesses and organizations to manage and analyze data. One of the most commonly used functions in Excel is VLOOKUP, which is used to search for specific data in a table and retrieve the corresponding data. In this article, we will provide a step-by-step guide on how to use VLOOKUP in Excel.
Step 1: Understanding VLOOKUP
VLOOKUP stands for vertical lookup. It is a function in Excel that allows you to search for a specific value in the first column of a table, and then retrieve a value in the same row or a specified column to the right. The syntax for VLOOKUP is as follows:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Lookup_value: The value you want to look up in the first column of the table.
Table_array: The range of cells that contains the table of data you want to search.
Col_index_num: The column number of the table that contains the data you want to retrieve.
Range_lookup: Optional. This is a logical value that indicates whether you want an exact match or an approximate match. If this value is TRUE or omitted, an approximate match will be used. If this value is FALSE, an exact match will be used.
Step 2: Creating a Table
Before you can use VLOOKUP, you need to create a table of data that contains the information you want to search and retrieve. For this example, we will create a table of student grades:
Open a new Excel workbook.
In cell A1, type “Name.”
In cell B1, type “Grade.”
In cells A2 to A6, type the names of five students.
In cells B2 to B6, type their grades.
Step 3: Using VLOOKUP
Now that we have created our table, we can use VLOOKUP to search for specific grades. For example, we can use VLOOKUP to search for the grade of a student named “John.” Here’s how:
In cell D2, type “John” (without the quotes).
In cell E2, enter the following formula:
=VLOOKUP(D2,A2:B6,2,FALSE)
Press Enter.
This formula tells Excel to search for the value in cell D2 (“John”) in the first column of the table (A2:B6), and retrieve the value in the second column (Grade). The “FALSE” at the end of the formula tells Excel to look for an exact match.
Step 4: Understanding the Result
When you press Enter, Excel will display the grade for John in cell E2. If John’s grade is in cell B2, then the value in cell E2 should be the same as the value in cell B2. If John’s name does not appear in the table, Excel will display an error message.
Step 5: Copying the Formula
Now that we have successfully used VLOOKUP to retrieve John’s grade, we can copy the formula to retrieve the grades for the other students. Here’s how:
Select cell E2.
Click and drag the fill handle (the small square in the bottom-right corner of the cell) down to cell E6.
Release the mouse button.
Excel will automatically adjust the formula for each student’s name.
Step 6: Adding More Columns
Sometimes, you may want to retrieve data from a different column in the table. For example, you may want to retrieve the student’s age instead of their grade. Here’s how:
In cell C1, type “Age.”
In cells
C2 to C6, type the ages of the five students.
3. In cell F1, type “Name.”
In cell G1, type “Age.”
In cell F2, type “John” (without the quotes).
In cell G2, enter the following formula:
=VLOOKUP(F2,A2:C6,3,FALSE)
Press Enter.
This formula tells Excel to search for the value in cell F2 (“John”) in the first column of the table (A2:C6), and retrieve the value in the third column (Age). The “FALSE” at the end of the formula tells Excel to look for an exact match.
Step 7: Understanding Nested Functions
Sometimes, you may want to use VLOOKUP within another function. For example, you may want to use VLOOKUP to retrieve a discount rate based on a customer’s order value, and then use that discount rate to calculate the discounted price. Here’s how:
In cell H1, type “Order Value.”
In cell H2, enter the order value (e.g., 1000).
In cell I1, type “Discount Rate.”
In cell I2, enter the following formula:
=VLOOKUP(H2,{0,0;500,0.1;1000,0.2;5000,0.3},2,TRUE)
Press Enter.
This formula tells Excel to search for the value in cell H2 in the first column of the table ({0,0;500,0.1;1000,0.2;5000,0.3}), and retrieve the value in the second column (Discount Rate). The “TRUE” at the end of the formula tells Excel to look for an approximate match.
In cell J1, type “Discounted Price.”
In cell J2, enter the following formula:
=H2*(1-I2)
Press Enter.
This formula tells Excel to multiply the order value in cell H2 by (1 – the discount rate in cell I2).
Step 8: Handling Errors
If the value you are looking for is not found in the table, or if there is an error in the formula, Excel will display an error message. Here are some common errors you may encounter when using VLOOKUP:
#N/A: This error message means that the value you are looking for was not found in the table. Double-check that the value is spelled correctly and that it is in the first column of the table.
#REF!: This error message means that the formula contains a reference to a cell that has been deleted or moved. Check the formula for any references that may be incorrect.
#VALUE!: This error message means that the formula contains an invalid argument or operand. Check the formula for any values that may be incorrect.
To handle errors in VLOOKUP, you can use the IFERROR function. Here’s how:
In cell K1, type “Lookup Value.”
In cell K2, enter a value that is not in the table (e.g., “Mary”).
In cell L1, type “Grade.”
In cell L2, enter the following formula:
=IFERROR(VLOOKUP(K2,A2:B6,2,FALSE),”Not Found”)
Press Enter.
This formula tells Excel to use VLOOKUP to search for the value in cell K2 in the first column of the table (A2:B6), and retrieve the value in the second column (Grade). If the value is not found, the formula will return “Not Found” instead of an error message.
Step 9: Using Absolute References
When you copy a formula that contains cell references, Excel automatically adjusts the references to reflect their new location. However, sometimes you want to prevent Excel from adjusting the references. To do this, you can use absolute references. Here’s how:
In cell M1, type “Total Grade.”
In cell M2, enter the following formula:
=SUM(B2:B6)
Press Enter.
This formula tells Excel to add up the grades in cells B2 to B6.
Now, we want to use VLOOKUP to calculate the average grade for each student. Here’s how:
In cell N1, type “Average Grade.”
In cell N2, enter the following formula:
=VLOOKUP(A2,A:B,2,FALSE)
Press Enter.
This formula tells Excel to search for the value in cell A2 in the first column of the table (A:B), and retrieve the value in the second column (Grade).
Now, we want to divide the total grade in cell M2 by the number of students to get the average grade. Here’s how:
In cell O1, type “Number of Students.”
In cell O2, enter the value 5.
In cell P1, type “Final Average Grade.”
In cell P2, enter the following formula:
=M2/(O2)
Press Enter.
This formula tells Excel to divide the total grade in cell M2 by the value in cell O2 (the number of students).
Now, let’s copy the formula in cell P2 to the rest of the cells in column P. However, we want to prevent Excel from adjusting the reference to cell O2. Here’s how:
Select cell P2.
Press F4.
This will add dollar signs ($) to the reference to cell O2, making it an absolute reference.
Click and drag the fill handle down to cell P6.
Excel will copy the formula to the rest of the cells in column P, but it will use the absolute reference to cell O2 in each formula.
Frequently Asked Questions:
What is VLOOKUP used for in Excel?
VLOOKUP is used to search for a specific value in the first column of a table and retrieve the corresponding value in the same row or a specified column to the right.
How do I use VLOOKUP in Excel?
To use VLOOKUP in Excel, you need to specify the value you want to look up, the range of cells that contains the table of data you want to search, the column number of the table that contains the data you want to retrieve, and whether you want an exact match or an approximate match.
What is the syntax for VLOOKUP in Excel?
The syntax for VLOOKUP in Excel is as follows: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
What is an absolute reference in Excel?
An absolute reference in Excel is a reference to a specific cell that does not change when the formula is copied to other cells. Absolute references are denoted by dollar signs ($) before the column letter and row number of the cell reference.
How do I handle errors in VLOOKUP?
You can handle errors in VLOOKUP by using the IFERROR function. This function allows you to specify a value or formula to display if an error occurs.
Can VLOOKUP search for values in multiple columns?
No, VLOOKUP can only search for values in the first column of a table.
Can VLOOKUP search for values in multiple sheets?
Yes, VLOOKUP can search for values in multiple sheets by using a combination of the INDIRECT function and the sheet name as part of the table array.
What is the difference between TRUE and FALSE in the range_lookup argument of VLOOKUP?
When the range_lookup argument is set to TRUE or omitted, VLOOKUP will look for an approximate match. This means that it will search for the closest value that is less than or equal to the lookup value. When the range_lookup argument is set to FALSE, VLOOKUP will look for an exact match.
Can VLOOKUP be used to search for text values?
Yes, VLOOKUP can be used to search for text values. However, you must make sure that the lookup value and the values in the first column of the table are formatted as text.
Is VLOOKUP case-sensitive?
Yes, VLOOKUP is case-sensitive by default. If you want to perform a case-insensitive search, you can use the UPPER or LOWER function to convert the lookup value and the values in the first column of the table to uppercase or lowercase before using VLOOKUP.
