How to vlookup in Excel?

To VLOOKUP in Excel is to efficiently search for a specific piece of information in one column of a table and retrieve a corresponding value from another column in the same row. It stands for "Vertical Lookup," indicating its ability to search data arranged vertically.

The VLOOKUP function is a powerful tool for data retrieval, enabling you to find related data across large datasets.

Understanding the VLOOKUP Function

The core of VLOOKUP lies in its arguments, which define what you're looking for, where to find it, and what to return. In its simplest form, the VLOOKUP function is structured as follows:

=VLOOKUP(What you want to look up, where you want to look for it, the column number in the range containing the value to return, return an Approximate or Exact match – indicated as 1/TRUE, or 0/FALSE)

Let's break down each argument:

• lookup_value: This is the value you want to search for. It could be text, a number, or a cell reference. For instance, if you want to find the price of "Apples," "Apples" would be your lookup_value.
• table_array: This is the range of cells where you want to perform the lookup. Crucially, the lookup_value must be in the first column of this table_array.
• col_index_num: This is the column number within your table_array from which you want to retrieve the corresponding value. The first column in your table_array is 1, the second is 2, and so on.
• range_lookup: This optional argument specifies whether you want an approximate match or an exact match:
  • FALSE (or 0): For an exact match. This is most commonly used and recommended when you need to find an exact correspondence (e.g., a specific product ID). If an exact match isn't found, VLOOKUP will return #N/A.
  • TRUE (or 1): For an approximate match. This requires your lookup column to be sorted in ascending order. It finds the closest match that is less than or equal to your lookup_value. This is useful for things like grading scales or tax brackets.

Step-by-Step Guide to Using VLOOKUP

Follow these steps to effectively implement VLOOKUP in your Excel spreadsheets:

1. Identify Your Data: Determine the value you want to look up (lookup_value) and the table or range where you will search for it (table_array).
2. Choose Your Return Column: Decide which column in your table_array contains the information you want to retrieve once a match is found. Note its column number relative to the start of your table_array.
3. Select the Destination Cell: Click on the cell where you want the VLOOKUP formula to display the result.
4. Enter the Formula: Type =VLOOKUP( and then input each argument separated by commas.
   • Example: If you are looking for "Product A" in a table from A2 to C10, and you want to return the value from the 3rd column, using an exact match, your formula might look like: =VLOOKUP("Product A", A2:C10, 3, FALSE).
5. Press Enter: The formula will calculate and display the result.
6. Drag Down (Optional): If you need to apply the VLOOKUP to multiple rows, consider making your table_array an absolute reference (e.g., A$2:C$10) by pressing F4 after selecting the range, before dragging the formula down. This ensures the table_array doesn't shift.

Practical Example

Let's say you have a list of employee IDs and want to find their corresponding departments from a separate master list.

Master Employee Data (Sheet1):

Your Working Sheet (Sheet2):

To find the Department for Employee ID 102 in Sheet2, using the data from Sheet1:

1. In Sheet2, select cell B2.
2. Enter the formula: =VLOOKUP(A2, Sheet1!A:C, 3, FALSE)
   • A2: This is the lookup_value (Employee ID 102).
   • Sheet1!A:C: This is the table_array. We are looking in columns A, B, and C of Sheet1. Note: Employee ID (our lookup_value) is in the first column (A) of this range.
   • 3: The col_index_num. Department is the 3rd column in our table_array (A=1, B=2, C=3).
   • FALSE: We want an exact match for the Employee ID.
3. Press Enter. Cell B2 will now display "Marketing".
4. You can then drag the formula down to B3 to find the department for Employee ID 104. For dragging, it's often better to use absolute references for the table_array, e.g., Sheet1!$A:$C or Sheet1!$A$2:$C$5.

Important Considerations and Tips

• First Column Rule: Remember, VLOOKUP always searches for the lookup_value in the first column of the table_array. If your lookup value is not in the first column of the specified range, VLOOKUP will not work correctly.
• Absolute References ($): When copying VLOOKUP formulas to other cells, especially when filling down a column, make sure to use absolute references (e.g., A$2:C$10 or $A$2:$C$10) for your table_array to prevent it from shifting.
• Error Handling (IFERROR): If VLOOKUP cannot find a match, it returns an #N/A error. To make your worksheets cleaner, you can wrap VLOOKUP in the IFERROR function:
  =IFERROR(VLOOKUP(lookup_value, table_array, col_index_num, range_lookup), "Not Found")
  This will display "Not Found" or any other specified text instead of #N/A.
• Case Sensitivity: VLOOKUP is generally not case-sensitive for exact matches. "Product A" will match "product a".
• Data Consistency: Ensure your lookup values are consistent. Extra spaces, hidden characters, or slight misspellings will prevent VLOOKUP from finding a match. The TRIM function can help remove leading/trailing spaces.
• Alternatives: While VLOOKUP is powerful, for more complex lookups (e.g., looking up values to the left, or multiple criteria), consider using:
  • INDEX and MATCH: A more flexible combination that allows you to look up values in any column, not just the first.
  • XLOOKUP: Available in newer versions of Excel, XLOOKUP is designed to be a more modern and versatile replacement for VLOOKUP and HLOOKUP, handling left lookups, approximate matches, and more with simpler syntax.

Understanding and correctly applying VLOOKUP can significantly streamline your data analysis and reporting tasks in Excel.