← Back to the activity

Year 11 Computing — 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.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)

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

  3. 3.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)

  4. 4.The first column of this table holds numbers rather than words: A1:A3 hold 101, 102 and 103, and B1:B3 hold 40, 55 and 70. What number does this formula show?

    =VLOOKUP(102, A1:B3, 2, FALSE)

  5. 5.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)

  6. 6.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)

  7. 7.In the formula =VLOOKUP("cup", A1:C4, 2, FALSE), what does the 2 mean?

    • a) Which row of the table to look in
    • b) That the answer it shows has to be more than 2
    • c) Which column of the table the answer is taken from, counting the table's own first column as 1
    • d) How many matching rows the formula should expect to find

  8. 8.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"
    • "bag"
    • "hat"
    • "jug"
    • "tin"

Answer key — Year 11 Computing — Looking a value up in a table

  1. 1. 2
  2. 2. c) Nothing is found, because the row holding pen is not inside the range the formula was given
  3. 3. 25
  4. 4. 55
  5. 5. 7
  6. 6. 225
  7. 7. c) Which column of the table the answer is taken from, counting the table's own first column as 1
  8. 8. "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