facebook twitter pinterest google plus Youtube
www.TestsTestsTests.com

www.TestsTestsTests.com

VLOOKUP & INDEX-MATCH Excel Test

Using Formulas to Look Up Data
Excel 2010 Training - Working with Data
Free Online Microsoft Excel Test

* Using VLOOKUP
* Using INDEX-MATCH

10 Question Multiple Choice Quiz with Answers
and Answer Explanations

VLOOKUP & INDEX-MATCH Excel Test


1) Which of the formulas below contain the correct syntax (formula arguments) for the VLOOKUP function?
a) =VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)
b) =VLOOKUP(table_array, lookup_value, col_index_num, range_lookup)
c) =VLOOKUP(lookup_value, table_array, col_index_num, value)
d) =VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

Question 2 - Using VLOOKUP & INDEX-MATCH Excel Test
2) Study the screenshot above.  If I entered the formula:  =INDEX(A2:A10,5) into a blank cell, what would the result be?
a) The result would be the number 5.
b) The result would be Casey.
c) The result would be Julian.
d) The result would be Lane.


Question 3 - Using VLOOKUP & INDEX-MATCH Excel Test
3) Study the screenshot above.  Using the INDEX-MATCH function, which of the formulas below, when entered into the highlighted cell (E3), will yield the result Group result (32A)?
a) =MATCH(B2:B9,INDEX(D3,A2:A9,0))
b) =INDEX(B2:B9,MATCH(D3,A2:A9,0))
c) =INDEX(B2:B9,MATCH(A2:A9,0))
d) =INDEX(MATCH(D3,A2:A9,0))

4) If you wanted to replace the INDEX-MATCH formula, used in question 3 above, with a VLOOKUP formula, which of the following would yield the same result as the INDEX-MATCH formula, when entered into the highlighted cell in the screenshot above question 3?
a) =VLOOKUP(A2:B9,2,FALSE) 
b) =VLOOKUP(D3,A2:B9,FALSE)
c) =VLOOKUP(D3,A2,2,FALSE)
d) =VLOOKUP(D3,A2:B9,2,FALSE)

5) In a VLOOKUP formula, what does the argument column_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.


Question 6 - Using VLOOKUP & INDEX-MATCH Excel Test
6) Study the formula in the screenshot above.  What should you do before copying this formula down to the seven cells below it?
a) Make the cell references ('Product List'!B2:B16 and 'Product List'!A2:A16), absolute.
b) Make all the cell references ('Product List'!B2:B16, 'Product List'!A2:A16 and D2), absolute.
c) Make the cell references below the selected cell, absolute.
d) Select the cells into which you want to copy the formula first before copying the formula down.

7) Which of the following is NOT possible with VLOOKUP?
a) You can lookup values located in a different worksheet.
b) You can lookup values located in a column to the right of the column that contains the lookup value.
c) You can lookup values located in a column to the left of the column that contains the lookup value.
d) You can lookup values such as text, numbers or characters.



8) What type of result does the MATCH function, when used on its own, return?
a) It returns the lookup value located in a specific location.
b) It returns a value that is the same as the lookup value.
c) It returns the cell reference of the lookup value.
d) It returns the relative position of a lookup value, either as a row or column number, within the selected array.

Question 9 - Using VLOOKUP & INDEX-MATCH Excel Test
9) Study the screenshot above.  To find the price for the selected product (coffee in the example above), what formula would you type into the highlighted cell (B19)?
a) =VLOOKUP(A19,A2:C16,3,FALSE)
b) =INDEX(C2:C16,MATCH(A19,A2:A16,0))
c) Both options a and b are correct.
d) Only option a is correct.
Question 10 - Using VLOOKUP & INDEX-MATCH Excel Test
10) Study the screenshot above.  To find the product name that matches the product code entered, which of the following formulas would you type into the highlighted cell (B19)?
a) =VLOOKUP(A19,A2:C16,1,FALSE)
b) =INDEX(A2:A16,MATCH(A19,B2:B16,0))
c) =INDEX(B2:B16,MATCH(A19,A2:A16,0))
d) =VLOOKUP(A19,A2:C16,1,TRUE)