www.TestsTestsTests.com

Microsoft Excel FREE Resources Index Page

Excel HLOOKUP, VLOOKUP & INDEX-MATCH Test – Excel Formulas & Excel Functions 2016

Excel HLOOKUP, VLOOKUP & INDEX-MATCH Test

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

 10 Question Multiple Choice Quiz with Answers and Answer Explanations ***GO TO THE TEST ANSWER EXPLANATIONS PAGE

Excel HLOOKUP, VLOOKUP & INDEX-MATCH Test

1) Sara has been asked to find a city with a population of 5 million people from the table above, she tries: =MATCH(5000000, D2:D6, 0) What result does she get?
a) 2
b) 3
c) 4
d) #N/A

2) What would the result be if Sara changed the formula from question 1 as follows: =MATCH(5000000, D2:D6)
a) 1
b) 2
c) 3
d) #N/A

3) Which of the following would result in a #REF error?
a) =INDEX(A2:D8, 1, 2)
b) =INDEX(A2:D2, 2)
c) =INDEX(A2:A8, 1, 1)
d) =INDEX(A2:B8, 1, 3)

4) Neil’s boss asked him to write a formula to work out the population for a given city. Neil found multiple examples of INDEX-MATCH formulas online, which of the following formulas will NOT give you the population of Moscow?
a) =INDEX(A2:D8, MATCH(“Moscow”, A2:A8, 0), 4)
b) =INDEX(D2:D8, MATCH(“Moscow”, A2:A8, 0))
c) =INDEX(D2:D8, MATCH(“Moscow”, A2:D8, 0), 1)
d) =INDEX(C2:D8, MATCH(“Moscow”, A2:A8, 0), 2)

5) Neil’s boss asked him to rewrite his formula using VLOOKUP. Which of the following VLOOKUP formulas would yield the same result as the INDEX-MATCH in question 5?
a) =VLOOKUP(A2:D8, “Moscow”, 4)
b) =VLOOKUP(“Moscow”, A2:D8, 4)
c) =VLOOKUP(“Moscow”, A2:A8, D2:D8)
d) =VLOOKUP(“Moscow”, 4, A2:D8)

6) The table above is a list of 2018 Academy award nominees for Best Actress. Briana is asked to write a formula to determine whether Glenn Close won the award. Which of the following formulas worked for her?
a) =VLOOKUP(“Won”, A3:D7, 1, FALSE)
b) =VLOOKUP(“Won”, A3:D7, A, FALSE)
c) =VLOOKUP(“Glenn Close”, A3:D7, D, FALSE)
d) =VLOOKUP(“Glenn Close”, A3:D7, 4, FALSE)

7) Briana asks if anyone knows how 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 her?
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))

8) Why could Briana not solve her problem from question 7 using VLOOKUP?
a) VLOOKUP cannot look up values from the column to the left of the column that contains the lookup value
b) VLOOKUP only works if the lookup_value column is in alphabetical order
c) Both a and b
d) Neither a nor b

9) Which of the following statements are TRUE regarding using wildcard characters for the lookup_value argument in HLOOKUP and VLOOKUP?
a) ? can be used as a place marker for a single character, and * can be used for character sequences of any length
b) Will only work when lookup_value is a text string
c) Will not work if range_lookup is TRUE
d) All of the above

10) Russel’s colleague, Pete, wrote the following formula to identify the film Yalitza Aparicio was nominated for:
=HLOOKUP(“Film”, A10:D15, 2)
Russel’s boss wants him to improve it, such that it would work for any actress’s name typed into cell B9. Which formula could Russel use?

a) =HLOOKUP(“Film”, A10:D15, MATCH(B9, A10:A15, 0))
b) =VLOOKUP(B1, A10:D15, MATCH(“Film”, A10:D10, 0), FALSE)
c) =HLOOKUP(“Film”, A11:D15, INDEX(B10:B15, B9))
d) Either a or b