facebook twitter pinterest google plus Youtube


VLOOKUP in Excel 2016 Test

Excel Formulas & Excel Functions 2016
Excel 2016 Training
Free Online Microsoft Excel Test
* Excel VLOOKUP formula
* Excel INDEX-MATCH formula

10 Question Multiple Choice Quiz with Answers
and Answer Explanations

* Go to the 2010 VLOOKUP Tutorial - INDEX-MATCH & VLOOKUP in Excel

VLOOKUP in Excel 2016 Test
INDEX-MATCH & VLOOKUP formula in Excel

1) Darius is testing his students on Excel formulas. Which of the following answers contains an error that would give a result of #REF?
a) =INDEX(A2:D7, 1, 2)
b) =INDEX(A2:D2, 2)
c) =INDEX(A2:A7, 1)
d) =INDEX(A2:D7, 1)

Question 2 - VLOOKUP in Excel 2016 Test

2) Darius asked his students, based on the table above, what the result of the following would be: =MATCH(5, D1:D7, 1) Which answer is correct?
a) 4
b) #REF
c) 3
d) 5

Question 3 - VLOOKUP in Excel 2016 Test

3) Terrence is writing a test. In a question asking him to find the weight of the Dalmation, his answer was: =VLOOKUP("Dalmation", B2:D7, 3, FALSE) What would be the result of his suggested formula be?
a) 4
b) 27kg
c) Pongo
d) Border-collie

4) Which of the following INDEX-MATCH formulas would yield the same result as Terrence’s answer in question 3?
a) =INDEX(B2:B7,MATCH("Dalmation",D2:D7,0)) 
b) =INDEX(D2:D7,MATCH("Dalmation",B2:B7,0))
c) =MATCH(D2:D7,INDEX("Dalmation",B2:B7,0))
d) =INDEX(B2:D7,MATCH("Dalmation",B2:B7,0))

Question 5 Excel VLOOKUP Test
5) The table above is a list of 2017 Academy award nominees for Best Actor. Bruce is asked to write a formula to determine whether Daniel Kaluuya won the award. Which of the following formulas worked for him?
a) =VLOOKUP(“Won”, A3:D7, 1, TRUE)
b) =VLOOKUP(“Won”, A3:D7, A, FALSE)
c) =VLOOKUP("Daniel Kaluuya", A3:D7, 4, TRUE)
d) =VLOOKUP(“Daniel Kaluuya”, A3:D7, D,TRUE)

6) Bruce suggests that it would be better to find the name of the winner, rather than check each name to see if they won. Using your knowledge of INDEX-MATCH functions, which of these formulas would you suggest to him?
a) =INDEX(A3:D7, MATCH(“Won”, A3:D7, 4), 1)
b) =INDEX(A3:D7, MATCH(“Won”, D3:D7))
c) =INDEX(A3:A7, MATCH("Won", D3:D7, 0))
d) =MATCH(D3:D7, INDEX(“Won”, A3:A7))

Question 7 - VLOOKUP in Excel 2016 Test
7) Dennis is asked to adapt Bruce’s formula from Question 5, in order to check whether The Shape of Water was associated with a Best Actress award (see table above). Which of the following formulas does this correctly?
a) =VLOOKUP("The Shape of Water", C3:D7, 2, FALSE)
b) =VLOOKUP("The Shape of Water", A3:D7, 1, FALSE)
c) =VLOOKUP("The Shape of Water", A3:D7, 4, FALSE)
d) =VLOOKUP("The Shape of Water", C3:D7, 1, FALSE)

8) Dennis's colleague, Richard, wrote the following formula to identify the role Margot Robbie played in the film she was nominated for:
  =HLOOKUP("Role", A2:D7, 4)
Dennis's boss wants him to improve it, such that it would work for any actress’s name typed into cell B1. Which formula could Dennis use?

a) =HLOOKUP("Role", A3:D7, INDEX(B1, B2:B7))
b) =HLOOKUP("Role", A2:D7, MATCH(B1, A2:A7, 0))
c) =VLOOKUP(B1, A2:D7, MATCH("Role", A2:D2, 0), FALSE)
d) Either b or c.

9) Which of the following statements are TRUE regarding using wildcard characters for the lookup_value argument in a MATCH formula?
a) Requires match_type to be set to 1
b) * can be used as a place marker for a single character, and ? can be used for character sequences of any length
c) Neither a nor b
d) Both a and b

10) Which of the following CANNOT be used for lookup_value in MATCH, VLOOKUP and HLOOKUP functions?
a) Numbers and Text
b) Cell References
c) Logical values
d) None of the above

* More from Tests Tests Tests.com