Bird
Raised Fist0
Excelspreadsheet~5 mins

VLOOKUP function in Excel - Cheat Sheet & Quick Revision

Choose your learning style10 modes available
LearnWhyDeepSheetTryChallengeScenarioRecallDash
Recall & Review
beginner
What does the VLOOKUP function do in Excel?
VLOOKUP searches for a value in the first column of a table and returns a value in the same row from a specified column.
Click to reveal answer
beginner
What are the four arguments of the VLOOKUP function?
The four arguments are:
1. Lookup_value: The value to find.
2. Table_array: The range where to search.
3. Col_index_num: The column number to return the value from.
4. Range_lookup: TRUE for approximate match, FALSE for exact match.
Click to reveal answer
beginner
What happens if you set the Range_lookup argument to FALSE?
VLOOKUP will look for an exact match of the lookup value. If it doesn't find one, it returns #N/A error.
Click to reveal answer
intermediate
How does VLOOKUP behave if the lookup value is not in the first column of the table array?
VLOOKUP only searches in the first column of the table array. If the lookup value is not there, it will not find it and may return #N/A.
Click to reveal answer
beginner
Why might you use FALSE instead of TRUE for the Range_lookup argument?
Using FALSE ensures you get an exact match, which is safer when you want precise data, like looking up a product code or ID.
Click to reveal answer
What does the third argument in VLOOKUP specify?
AThe column number to return the value from
BThe value to look for
CThe range to search in
DWhether to find an exact or approximate match
If you want VLOOKUP to find an exact match, what should the fourth argument be?
ATRUE
B0
CFALSE
D1
Where does VLOOKUP look for the lookup value in the table array?
AIn the first column
BIn the last column
CIn any column
DIn the middle column
What error does VLOOKUP return if it can’t find the lookup value with exact match?
A#VALUE!
B#N/A
C#REF!
D#DIV/0!
Which of these is a correct VLOOKUP formula to find a price in column 3 for product ID in A2 within range B2:D10?
A=VLOOKUP(A2, B2:D10, 4, FALSE)
B=VLOOKUP(B2, A2:D10, 3, TRUE)
C=VLOOKUP(A2, B2:D10, 2, TRUE)
D=VLOOKUP(A2, B2:D10, 3, FALSE)
Explain how to use VLOOKUP to find a person's phone number from a list where names are in the first column and phone numbers in the second.
Think about what you want to find, where to look, and which column has the answer.
You got /4 concepts.
    Describe what happens if the lookup value is not found when using VLOOKUP with exact match.
    Consider the result when the value is missing and how VLOOKUP signals it.
    You got /3 concepts.

      Practice

      (1/5)
      1. What does the VLOOKUP function do in Excel?
      easy
      A. It sorts data alphabetically.
      B. It adds up all numbers in a column.
      C. It creates a chart from selected data.
      D. It looks for a value in the first column of a table and returns a value from the same row.

      Solution

      1. Step 1: Understand VLOOKUP purpose

        VLOOKUP searches for a value in the first column of a table.

      2. Step 2: Identify what it returns

        It returns a value from the same row but a different column you specify.

      3. Final Answer:

        It looks for a value in the first column of a table and returns a value from the same row. -> Option D

      4. Quick Check:

        VLOOKUP finds and returns matching row data [OK]
      Hint: VLOOKUP always searches first column, returns from chosen column [OK]
      Common Mistakes:
      • Thinking VLOOKUP sums or sorts data
      • Confusing VLOOKUP with chart tools
      • Assuming it searches any column, not just first
      2. Which of these is the correct syntax for an exact match VLOOKUP formula to find value in A2 in table B2:D10 and return column 3?
      easy
      A. =VLOOKUP(A2, B2:D10, 2, FALSE)
      B. =VLOOKUP(A2, B2:D10, 3, TRUE)
      C. =VLOOKUP(A2, B2:D10, 3, FALSE)
      D. =VLOOKUP(A2, B2:D10, 3)

      Solution

      1. Step 1: Identify exact match argument

        For exact match, the fourth argument must be FALSE.

      2. Step 2: Check column index and range

        We want column 3 from range B2:D10, so column index is 3.

      3. Final Answer:

        =VLOOKUP(A2, B2:D10, 3, FALSE) -> Option C

      4. Quick Check:

        Exact match needs FALSE as last argument [OK]
      Hint: Use FALSE for exact match, TRUE for approximate [OK]
      Common Mistakes:
      • Using TRUE instead of FALSE for exact match
      • Forgetting the column number or using wrong one
      • Omitting the last argument defaults to TRUE (approximate)
      3. Given this table in cells A1:B4:

      Name | Score
      John | 85
      Mary | 92
      Alex | 78

      What is the result of =VLOOKUP("Mary", A2:B4, 2, FALSE)?
      medium
      A. 85
      B. 92
      C. Mary
      D. #N/A

      Solution

      1. Step 1: Locate lookup value in first column

        "Mary" is found in cell A3.

      2. Step 2: Return value from column 2 same row

        Column 2 in row 3 is 92.

      3. Final Answer:

        92 -> Option B

      4. Quick Check:

        VLOOKUP returns matching row's column 2 value [OK]
      Hint: VLOOKUP returns value from specified column in matched row [OK]
      Common Mistakes:
      • Returning value from wrong column index
      • Getting #N/A if exact match not found
      • Confusing lookup value with return value
      4. You wrote =VLOOKUP(B2, A2:C10, 4, FALSE) but get an error. What is the problem?
      medium
      A. The column index 4 is outside the table range columns.
      B. The lookup value B2 is missing.
      C. The range A2:C10 is not sorted.
      D. The FALSE argument is invalid.

      Solution

      1. Step 1: Check table range columns

        Range A2:C10 has 3 columns only.

      2. Step 2: Verify column index argument

        Column index 4 is invalid because it exceeds the number of columns in the range.

      3. Final Answer:

        The column index 4 is outside the table range columns. -> Option A

      4. Quick Check:

        Column index must be ≤ number of columns in range [OK]
      Hint: Column index can't be bigger than table columns count [OK]
      Common Mistakes:
      • Using column index larger than table columns
      • Assuming FALSE is invalid argument
      • Thinking lookup value must be in range
      5. You have a product list in A2:C6:

      Code | Name | Price
      P01 | Pen | 1.5
      P02 | Book | 7.0
      P03 | Bag | 12.5
      P04 | Cup | 3.0
      P05 | Box | 4.5

      How would you write a formula to find the price of product code in E2 exactly, returning "Not found" if code is missing?
      hard
      A. =IFERROR(VLOOKUP(E2, A2:C6, 3, FALSE), "Not found")
      B. =VLOOKUP(E2, A2:C6, 3, TRUE)
      C. =VLOOKUP(E2, A2:C6, 2, FALSE)
      D. =IF(VLOOKUP(E2, A2:C6, 3, FALSE) = 0, "Not found", VLOOKUP(E2, A2:C6, 3, FALSE))

      Solution

      1. Step 1: Use VLOOKUP for exact match price

        VLOOKUP with FALSE finds exact product code and returns price from column 3.

      2. Step 2: Handle missing code with IFERROR

        Wrap VLOOKUP in IFERROR to return "Not found" if code is missing.

      3. Final Answer:

        =IFERROR(VLOOKUP(E2, A2:C6, 3, FALSE), "Not found") -> Option A

      4. Quick Check:

        IFERROR catches errors, shows custom message [OK]
      Hint: Wrap VLOOKUP in IFERROR to show message if no match [OK]
      Common Mistakes:
      • Using TRUE instead of FALSE for exact match
      • Returning wrong column index value
      • Not handling missing lookup values causing errors
      Recommended

      Ready to prove it?

      Pass the test and this pattern moves unknown strong while earning +1 Grip toward your certificate.

      10 questions~5 mineasy hard