1 Which function is used to search for a value in the first column of a table and return a value in the same row from a specified column?
a) HLOOKUP
b) INDEX
c) VLOOKUP
d) MATCH
Answer: c) VLOOKUP
2 What is the result of the formula =HLOOKUP(“Sales”, A1
, 2, FALSE) if “Sales” is found in the first row of the range A1?
a) The value in the second row of the column where “Sales” is found
b) The value in the first row of the column where “Sales” is found
c) #N/A
d) #VALUE!
Answer: a) The value in the second row of the column where “Sales” is found
3 Which function can be used to return the position of a value in a given range?
a) VLOOKUP
b) HLOOKUP
c) INDEX
d) MATCH
Answer: d) MATCH
4 In the formula =INDEX(B2, MATCH(“John”, A2, 0)), what does MATCH(“John”, A2, 0) return?
a) The value in column B corresponding to “John”
b) The row number where “John” is found in column A
c) The column number where “John” is found
d) The value in column A corresponding to “John”
Answer: b) The row number where “John” is found in column A
5 How does the formula =VLOOKUP(1001, A2, 3, TRUE) behave if 1001 is not an exact match but falls between 1000 and 2000 in the first column?
a) Returns the value in the third column of the row where 1001 falls between 1000 and 2000
b) Returns #N/A
c) Returns the value in the first row
d) Returns an error
Answer: a) Returns the value in the third column of the row where 1001 falls between 1000 and 2000
6 Which function is used to look up a value in the first row of a table and return a value in the same column from a specified row?
a) INDEX
b) MATCH
c) VLOOKUP
d) HLOOKUP
Answer: d) HLOOKUP
7 What does the formula =INDEX(A2, 5, 2) return?
a) The value in the 5th row and 2nd column of the range A2
b) The value in the 2nd row and 5th column of the range A2
c) The value in the 5th column of the range A2
d) The value in the 2nd column of the range A2
Answer: a) The value in the 5th row and 2nd column of the range A2
8 What does the formula =MATCH(45, B2, 0) return if 45 is found in the 4th position of the range B2?
a) 45
b) 4
c) #N/A
d) 3
Answer: b) 4
9 In the formula =HLOOKUP(“Revenue”, A1, 3, FALSE), what does FALSE signify?
a) The function will perform an approximate match
b) The function will perform an exact match
c) The function will use the column index number
d) The function will return an error
Answer: b) The function will perform an exact match
10 Which combination of functions is used to find the value in a specific row and column in a table?
a) INDEX and MATCH
b) VLOOKUP and HLOOKUP
c) MATCH and HLOOKUP
d) VLOOKUP and INDEX
Answer: a) INDEX and MATCH