facebook twitter pinterest google plus Youtube


How to use VLOOKUP in Excel 2016 Test

Excel Formulas & Excel Functions 2016
Excel 2016 Training
Free Online Microsoft Excel Test
* VLOOKUP in Excel
* HLOOKUP in Excel

10 Question Multiple Choice Quiz with Answers
and Answer Explanations

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

How to use VLOOKUP in Excel 2016 Test - INDEX-MATCH - HLOOKUP

Question 1 How to use VLOOKUP in Excel
1) The table above is a list of 2018 Academy award nominees for Best Actor. Candice is asked to write a formula to determine whether Rami Malek won the award. Which of the following formulas worked for him?
a) =VLOOKUP(“Won”, A11:D15, 1, TRUE)
b) =VLOOKUP(“Won”, A11:D15, A, FALSE)
c) =VLOOKUP(“Rami Malek”, A11:D15, 4, TRUE)
d) =VLOOKUP(“Rami Malek”, A11:D15, D,TRUE)

2) Which of the following formulas would return the name of the actor who won the award in 2018?
a) =INDEX(A11:A15, MATCH(“Won”, D11:D15, 0))
b) =VLOOKUP(“Actor”, A10:D15, MATCH(“Won”, D10:D15, 0))
c) =HLOOKUP(“Actor”, A10:D15, MATCH(“Won”,D10:D15, 0))
d) Both a and c

3) Candice is told by an intern that cell references can be used for the lookup_value parameter in a VLOOKUP function, so she attempts the following:
  =VLOOKUP(“B9”, A11:D15, 2)
Unfortunately, the results are not what she expected, and she concludes that the intern is mistaken. Why did it not work?
a) The intern is wrong, VLOOKUP can only use Numbers, Text or Logical Values as the lookup_value, not cell references.
b) The cell reference should not be placed within quotation marks.
c) The intern is wrong, VLOOKUP can only use Numbers or Text as the lookup_value, not logical values or cell references.
d) B9 is outside of the table_array range.

4) Which of character can be used as a “wildcard” representing just one unknown character in the lookup_value parameter of both HLOOKUP and VLOOKUP functions?
a) %
b) *
c) ?
d) _

5) Rajev gets a #REF error while attempting one of the INDEX formulas below, which of the formulas gave him the #REF error?
a) =INDEX(A2:D7, 1, 2)
b) =INDEX(A2:D2, 2)
c) =INDEX(A2:D7, 1)
d) =INDEX(A2:A7, 1)

Question 6 How to use VLOOKUP in Excel
6) Study the screenshot above. Warren is trying to get the publisher for the game Neverwinter, his solution was:
  =VLOOKUP(“Neverwinter”, B2:D7, 3, FALSE) What would be the result of his suggested formula be?
a) Dishonored
c) Cryptic
d) Perfect World

7) Warren’s boss asks him to do a similar VLOOKUP which will return the game title, given a specific publisher name. Warren responds to say that he can’t do it with VLOOKUP. Why?
a) VLOOKUP can’t lookup values located in a column to the left of the lookup value.
b) VLOOKUP is only able to match vertically to values below the lookup value.
c) VLOOKUP is only able to match vertically to values above the lookup value.
d) None of the above.

8) Warren realizes he can achieve his boss’s goal, of returning the name based on publisher, using a combination of the MATCH and INDEX functions, instead of VLOOKUP. How would he write that formula?
a) =INDEX(D2:D7, MATCH(“Perfect World”, A2:A7, 0))
b) =INDEX(A2:A7, MATCH(“Perfect World”, D2:D7, 0))
c) =MATCH(D2:D7,INDEX(“Perfect World”,A2:A7,0))
d) =MATCH(A2:A7, INDEX (“Perfect World”, D2:D7, 0))

9) Warren wrote the following formula to identify the genre of Darksiders:
  =HLOOKUP(“Darksiders”, A2:D7, 2)
Louise wants to improve it, such that it would work for any game’s name typed into cell A9. Which formula could she use?
a) =HLOOKUP(“Genre”, A1:D7, MATCH(A9, A1:A7, 0))
b) =VLOOKUP(A9, A1:D7, MATCH(“Genre”, A1:D1, 0), FALSE)
c) =HLOOKUP(“Genre”, A2:D7, INDEX(B2:B7, A9))
d) Either a or b.

10) In a VLOOKUP formula, what does the argument col_index_number mean?
a) This is the secret INDEX number that each column in an Excel worksheet has.
b) This is the number of the column within the selected table_array where the lookup result is located.
c) This is a numerical representation of the letters at the top of a worksheet.  For example, A = 1, B = 2, etc.
d) This is numbers you assign to columns in your worksheet.

* More from Tests Tests Tests.com