Practice VLOOKUP Function Online in Excel

Watch: How to use VLOOKUP Function in Excel?

What is VLOOKUP Function?

The VLOOKUP function in Excel searches for a value in a table and returns a corresponding value from another column in the same row Click here to Read Full Tutorial

What is VLOOKUP Function Syntax?
				
					=VLOOKUP(lookup_value, table_array, column_index_number, [rangelookup])

Practice 1: VLOOKUP Simple Example (First Method)

Click on any cell below to start practicing VLOOKUP Function. Check Instruction how to use the VLOOKUP Function.

Excel VBA Master Tool Kit
  • Click on cell B3: This is where you want the result to appear.
  • Type =VLOOKUP(: This starts the VLOOKUP formula.
  • Find the lookup value: Select cell A3 (this is the value you’re searching for).
  • Select the table array: Highlight the table where you want to search. For example, click and drag to select the range of cells from column A to column G (e.g., A1:G101).
  • Choose the column number: Decide which column has the value you want to show. If it’s the second column in your selection, type 2.
  • Press 0 for exact match: Type 0 (or FALSE) to make sure it finds an exact match.
  • Close the formula and press Enter: Type ) to close the formula and hit Enter.

Practice 2: Use VLOOKUP Function with Multiple Criteria

Click on any cell below to start practicing VLOOKUP Function with Multiple Criteria. Check Instruction how to use the VLOOKUP Function with Multiple Criteria.

  • Create a unique value: In the “Dummy Data” sheet, use the CONCAT function to combine two columns into one unique value. Example: =CONCAT(B2, A2).

  • Go to cell C3: Click on cell C3, where you want the result.

  • Type =VLOOKUP: Start the VLOOKUP formula.

  • Combine lookup values: Use B3 & A3 to join the two values you want to match.

  • Select the table array: Highlight the table in “Dummy Data” that contains the unique value.

  • Choose the column number: Enter the column number where the result is located.

  • Press Enter: Close the formula with ) and hit Enter.

Practice 3: Excel VLOOKUP With Named Range: Advanced Method

Click on any cell below to start practicing VLOOKUP Function with Name Range Criteria. Check Instruction how to use the VLOOKUP Function with Name Range Criteria.

    • Go to Name Manager: Create a name called “Lookup_Value” for your table range.
    • Click on cell B3: This is where you want the result.
    • Type =VLOOKUP: Start the formula.
    • Select the lookup value: Click on A3 (the value to search for).
    • Use the named range: For the table array, type “Lookup_Value”.
    • Enter the column number: Type the column number where the result is.
    • Type 0 for an exact match: Add 0 for exact matching.
    • Press Enter: Close the formula and hit Enter.

Practice 4: How to Use VLOOKUP in Excel Tables

Click on any cell below to start practicing VLOOKUP Function with Excel Table Criteria. Check Instruction how to use the VLOOKUP Function with Excel Table Criteria.

    • Go to Format as table: Create a Excel Table for your table range.
    • Click on cell B3: This is where you want the result.
    • Type =VLOOKUP: Start the formula.
    • Select the lookup value: Click on A3 (the value to search for).
    • Use the named range: For the table array, type “Lookup_Value”.
    • Enter the column number: Type the column number where the result is.
    • Type 0 for an exact match: Add 0 for exact matching.
    • Press Enter: Close the formula and hit Enter.

Practice 5: How to Use Excel Table VLOOKUP

Click on any cell below to start practicing VLOOKUP Function with Excel Table Criteria. Check Instruction how to use the VLOOKUP Function with Excel Table Criteria.

    • Go to Format as table: Create a Excel Table for your table range In Practice sheet.
    • Click on cell B3: This is where you want the result.
    • Type =VLOOKUP: Start the formula.
    • Select the lookup value: Click on A3 (the value to search for).
    • Use the named range: For the table array, type “Lookup_Value”.
    • Enter the column number: Type the column number where the result is.
    • Type 0 for an exact match: Add 0 for exact matching.
    • Press Enter: Close the formula and hit Enter.

Practice 6: Alternative of VLOOKUP

Click on any cell below to start practicing Alternative of VLOOKUP in Excel Check Instruction how to use the Alternative VLOOKUP Function in Excel.

  • INDEX: Finds and gives back a value from a specific row in a group of cells.
  • ‘Dummy Data’!$B$1:$B$101: This is the list of values (in Column B) that we want to pick from.
  • MATCH: Looks for a value and tells you which row it is in.
  • ‘VLOOKUP Method’!$I12: This is the value you are trying to find.
  • ‘Dummy Data’!$H$1:$H$101: This is the list of values (in Column H) where we search for the value in I12.
  • 0: This means we want an exact match (not a close or similar match).

Practice 7:Excel VLOOKUP Function Usage in Calculation

Click on any cell below to start practicing Excel VLOOKUP Function usage in Calculation Check Instruction how to use the Excel VLOOKUP Function usage in Calculation.

 

    • Click on cell B3: This is where you want the result.
    • Type =VLOOKUP: Start the formula.
    • Select the lookup value: Click on A3 (the value to search for).
    • Use the named range: For the table array, type “Lookup_Value”.
    • Enter the column number: Type the column number where the result is.
    • Type 0 for an exact match: Add 0 for exact matching.
    • Press Enter: Close the formula and hit Enter.
    • Multiply the column B3*A1

Practice 8:Excel VLOOKUP Function Usage in Data Validation

Click on any cell below to start practicing Excel VLOOKUP Function usage in Data Validation Check Instruction how to use the Excel VLOOKUP Function usage in Data Validation.

  •  Click on cell B3: This is where you want the result.
  • Type =VLOOKUP: Start the formula.
  • Select the lookup value: Click on A3 (the value to search for).
  • Use the named range: For the table array, type “Lookup_Value”.
  • Enter the column number: Type the column number where the result is.
  • Type 0 for an exact match: Add 0 for exact matching.
  • Press Enter: Close the formula and hit Enter.
  • Multiply the column B3*A1

Practice 9:Excel VLOOKUP Function Master Trick (Dynamic Way)

Click on any cell below to start practicing Excel VLOOKUP Function Master Trick (Dynamic Way) Check Instruction how to use the Excel VLOOKUP Function Master Trick (Dynamic Way).

  • VLOOKUP: Looks for something in the first column of a table and gives you something from the same row in another column.

  • $I21: This is what you’re looking for. It’s the value in cell I21.

  • Lookup_Data: This is the table where you’re searching. The first column of this table must have the values you’re looking for.

  • MATCH(J$20, ‘Dummy Data’!$A$1:$H$1, 0):

MATCH: Finds the position of the value in J$20 (a column title) from the headers in 'Dummy Data'!$A$1:$H$1.

Why it’s used: It tells VLOOKUP which column number to pull data from, based on the position of the title in J$20.

In MATCH: Makes sure it finds an exact match for the column title in J$20.In VLOOKUP: Makes sure it finds an exact match for the value in I21.

Similar Posts

How to use Excel Named range

Excel named range – how to define and use names in Excel

BySumit Shrimal

This tutorial explains what an Excel name is and shows you how to define a name for a cell, range, constant, or formula. You’ll also learn how to edit, filter, and delete defined names in Excel.
Excel names are a bit of a paradox: they’re one of the most useful features, but many people find them unnecessary or too technical. That’s because few users truly understand what Excel names can do. This tutorial will not only teach you how to create a named range in Excel but also show you how this feature can make your formulas easier to write, read, and reuse.

How to use IFERROR Function in Excel
| |

How to use IFERROR Function in Excel

BySumit Shrimal

This tutorial explains how to use the IFERROR function in Excel to catch and handle errors. It shows you how to replace errors with a blank cell, a different value, or a custom message. You’ll also learn how to use IFERROR with functions like VLOOKUP and INDEX MATCH, and how it compares to other error-checking functions like IF ISERROR and IFNA

Leave a Reply

Your email address will not be published. Required fields are marked *

Excel VBA Master Tool Kit