← Back to the activity

Class 10 Computer Science — Looking a value up in a table

Read a VLOOKUP: find the row its search value matches, and take the answer from the column its number names.

Name: ________________________

  1. 1.Two lookups into the same table are multiplied together here. Column A holds pen, bag, cup and map; column B holds 10, 40, 25 and 60; column C holds 5, 2, 9 and 4. What number does this formula show?

    =VLOOKUP("cup", A1:C4, 2, FALSE) * VLOOKUP("cup", A1:C4, 3, FALSE)

  2. 2.A table sits in A1:B4, with pen, bag, cup and map down column A and 30, 12, 45 and 7 beside them in column B. Put these lookups in order of the number each one shows, smallest first.

    • =VLOOKUP("pen", A1:B4, 2, FALSE)
    • =VLOOKUP("cup", A1:B4, 2, FALSE)
    • =VLOOKUP("bag", A1:B4, 2, FALSE)
    • =VLOOKUP("map", A1:B4, 2, FALSE)

  3. 3.A table sits in A1:B5. Column A holds pen, bag, cup, map and tin; column B holds 14, 9, 31, 6 and 22. Match each value being looked for to the number a lookup of column 2 shows for it.

    • "pen"
    • "cup"
    • "tin"
    • "bag"
    • "map"
    • 31
    • 14
    • 6
    • 9
    • 22

  4. 4.A table sits in C1:E4, away from the left edge of the sheet. Column C holds pen, bag, cup and map; column D holds 12, 30, 8 and 45; column E holds 7, 1, 6 and 3. What number does this formula show?

    =VLOOKUP("pen", C1:E4, 3, FALSE)

  5. 5.A sheet holds a table in A1:B4. Column A holds the names pen, bag, cup and map, and column B holds 10, 40, 25 and 60 beside them. What number does this formula show?

    =VLOOKUP("cup", A1:B4, 2, FALSE)

  6. 6.A table's first column holds pen, bag, cup and map, and the lookups below all use FALSE, which asks for an exact match. Sort each value being looked for by what the lookup does with it.

    Groups: Finds a row · Finds nothing, and the formula shows an error

    • "pen"
    • "map"
    • "hat"
    • "jug"
    • "tin"
    • "bag"

  7. 7.A table of names and prices fills A1:B5, and pen is the name in A1. Someone writes a lookup for pen but gives its range as A2:B5 by mistake. What happens?

    • a) It shows an answer one row further down than it should
    • b) It works, because a lookup searches the whole sheet anyway
    • c) It shows the price in B2, since that is the first row of the range
    • d) Nothing is found, because the row holding pen is not inside the range the formula was given

  8. 8.A sheet holds a table in A1:C4. Column A holds pen, bag, cup and map; column B holds 10, 40, 25 and 60; column C holds 5, 2, 9 and 4. What number does this formula show?

    =VLOOKUP("bag", A1:C4, 3, FALSE)

Answer key — Class 10 Computer Science — Looking a value up in a table

  1. 1. 225
  2. 2. 1. =VLOOKUP("map", A1:B4, 2, FALSE) 2. =VLOOKUP("bag", A1:B4, 2, FALSE) 3. =VLOOKUP("pen", A1:B4, 2, FALSE) 4. =VLOOKUP("cup", A1:B4, 2, FALSE)
  3. 3. "pen" → 14; "cup" → 31; "tin" → 22; "bag" → 9; "map" → 6
  4. 4. 7
  5. 5. 25
  6. 6. "bag" → Finds a row; "hat" → Finds nothing, and the formula shows an error; "map" → Finds a row; "tin" → Finds nothing, and the formula shows an error; "pen" → Finds a row; "jug" → Finds nothing, and the formula shows an error
  7. 7. d) Nothing is found, because the row holding pen is not inside the range the formula was given
  8. 8. 2