Question 1

In a FALSE VLOOKUP, if an exact match is not found, ____ is returned.

Accepted Answer

A)  an error value 
B)  an approximate match 
C)  the first value in the column 
D)  the last value in the column 
A)  an error value 
B)  an approximate match 
C)  the first value in the column 
D)  the last value in the column

Question 2

Violet is learning how to use the MATCH function and is testing several different formulas. The chart above shows orders for small robot toys from five different customers. The amount "0" in column A is for a customer who changed his mind. Please answer the questions below using the data in the figure above.
-Violet is not sure if the Sales department is filling in the word "return" in uppercase or lowercase letters. What is the correct way to write "return" in a MATCH formula?

Accepted Answer

A)  return 
B)  RETURN 
C)  Return 
D)  It doesn't matter; the MATCH formula does not distinguish between upper- and lowercase letters. 
A)  return 
B)  RETURN 
C)  Return 
D)  It doesn't matter; the MATCH formula does not distinguish between upper- and lowercase letters.

Question 3

With the INDEX function, if you are using a noncontiguous range, you must enclose the entire reference in ____.

Accepted Answer

A)  quotation marks 
B)  brackets 
C)  parentheses 
D)  none of the above 
A)  quotation marks 
B)  brackets 
C)  parentheses 
D)  none of the above

Question 4

The syntax of the HLOOKUP function is as follows: HLOOKUP(lookup_value,table_array,row_index_num,range_lookup).

Accepted Answer

A) True 
 B)False

Question 5

The ____________________ function returns the relative position of an item in a list, and can be used instead of LOOKUP, VLOOKUP, or HLOOKUP when you need the position of an item in a range instead of the item itself.

Accepted Answer

The answer of The ____________________ function returns the relative position...

Question 6

The ISBLANK function tests for a(n) ____.

Accepted Answer

A)  blank cell 
B)  error message resulting from the use of a blank cell in your formula 
C)  both a and b 
D)  neither a nor b 
A)  blank cell 
B)  error message resulting from the use of a blank cell in your formula 
C)  both a and b 
D)  neither a nor b

Question 7

MATCHING
Identify the letter of the choice that best matches the definition of the word or phrase.
-Allows you to retrieve data from multidimensional tables

Accepted Answer

A) vertical lookup table 
B) horizontal lookup table 
C) LOOKUP function 
D) INDEX function 
E) MATCH function
F)CHOOSE function
G)IFERROR 
A) vertical lookup table 
B) horizontal lookup table 
C) LOOKUP function 
D) INDEX function 
E) MATCH function
F)CHOOSE function
G)IFERROR

Question 8

When using an INDEX function, if you enter a row_num, column_num, or area_num outside of the range you referenced in the first argument, Excel displays ____.

Accepted Answer

A)  an error message 
B)  the INDEX wizard 
C)  the next nearest column or row 
D)  none of the above 
A)  an error message 
B)  the INDEX wizard 
C)  the next nearest column or row 
D)  none of the above

Question 9

MATCHING
Identify the letter of the choice that best matches the definition of the word or phrase.
-A lookup table in which the data to be searched is organized in rows

Accepted Answer

A) vertical lookup table 
B) horizontal lookup table 
C) LOOKUP function 
D) INDEX function 
E) MATCH function
F)CHOOSE function
G)IFERROR 
A) vertical lookup table 
B) horizontal lookup table 
C) LOOKUP function 
D) INDEX function 
E) MATCH function
F)CHOOSE function
G)IFERROR

Question 10

In a(n) two-dimensional table, Excel can search a row or column to find key data, and then use that data to locate the correct value. _________________________

Accepted Answer

A) True 
 B)False

Question 11

The IS function called ISLOGICAL is for values that refer to a logical value. _________________________

Accepted Answer

A) True 
 B)False

Question 12

When solving a(n) ____________________ formula, Excel looks up a value by testing for a criterion across a row, instead of down a column.

Accepted Answer

The answer of When solving a(n) ____________________ formula, Excel looks...

Question 13

The syntax of the INDEX function is as follows: INDEX(value,row_num,column_num,area_num). _________________________

Accepted Answer

A) True 
 B)False

Question 14

Mark is in charge of determining the unit pricing for each order for robot toys. Please refer to the chart above as you answer the questions below.
-Mark tests the following formula with a FALSE type in the worksheet shown above: =VLOOKUP(D14,A$3:B$8,2,FALSE). The result is ____.

Accepted Answer

A)  a price of $4.75 
B)  a price of $5.00 
C)  the #N/A error message 
D)  a price of $4.50 
A)  a price of $4.75 
B)  a price of $5.00 
C)  the #N/A error message 
D)  a price of $4.50

Question 15

The function named IFERROR combines aspects of the IF function and the ____ function.

Accepted Answer

A)  OFERROR 
B)  ISERROR 
C)  ISBLANK 
D)  VLOOKUP 
A)  OFERROR 
B)  ISERROR 
C)  ISBLANK 
D)  VLOOKUP

Question 16

The INDEX function allows you to retrieve data from multidimensional tables.

Accepted Answer

A) True 
 B)False

Question 17

With ____________________, you can specify your own text (or even a blank cell) when an error is encountered rather than the standard Excel message.

Accepted Answer

The answer of With ____________________, you can specify your own...

Question 18

In a LOOKUP function, the argument ____ refers to the location of the data you want to look up.

Accepted Answer

A)  lookup_value 
B)  lookup_vector 
C)  result_vector 
D)  lookup_range 
A)  lookup_value 
B)  lookup_vector 
C)  result_vector 
D)  lookup_range

Question 19

The ____ function is designed to return the relative position (such as 1, 2, or 3) of an item in a list.

Accepted Answer

A)  MATCH 
B)  RELATIVE 
C)  POSITION 
D)  LIST 
A)  MATCH 
B)  RELATIVE 
C)  POSITION 
D)  LIST

Question 20

If you use an INDEX formula that refers to ranges on separate worksheets, Excel displays the ____ error message.

Accepted Answer

A)  #VALUE 
B)  #REF! 
C)  #ERROR 
D)  none of the above 
A)  #VALUE 
B)  #REF! 
C)  #ERROR 
D)  none of the above

In a FALSE VLOOKUP, if an exact match is not found, ____ is returned.

With the INDEX function, if you are using a noncontiguous range, you must enclose the entire reference in ____.

The syntax of the HLOOKUP function is as follows: HLOOKUP(lookup_value,table_array,row_index_num,range_lookup).

The ____________________ function returns the relative position of an item in a list, and can be used instead of LOOKUP, VLOOKUP, or HLOOKUP when you need the position of an item in a range instead of the item itself.

The ISBLANK function tests for a(n) ____.

MATCHING Identify the letter of the choice that best matches the definition of the word or phrase. -Allows you to retrieve data from multidimensional tables

When using an INDEX function, if you enter a row_num, column_num, or area_num outside of the range you referenced in the first argument, Excel displays ____.

MATCHING Identify the letter of the choice that best matches the definition of the word or phrase. -A lookup table in which the data to be searched is organized in rows

In a(n) two-dimensional table, Excel can search a row or column to find key data, and then use that data to locate the correct value. _________________________

The IS function called ISLOGICAL is for values that refer to a logical value. _________________________

When solving a(n) ____________________ formula, Excel looks up a value by testing for a criterion across a row, instead of down a column.

The syntax of the INDEX function is as follows: INDEX(value,row_num,column_num,area_num). _________________________

Mark is in charge of determining the unit pricing for each order for robot toys. Please refer to the chart above as you answer the questions below. -Mark tests the following formula with a FALSE type in the worksheet shown above: =VLOOKUP(D14,A$3:B$8,2,FALSE). The result is ____.

The function named IFERROR combines aspects of the IF function and the ____ function.

The INDEX function allows you to retrieve data from multidimensional tables.

With ____________________, you can specify your own text (or even a blank cell) when an error is encountered rather than the standard Excel message.

In a LOOKUP function, the argument ____ refers to the location of the data you want to look up.

The ____ function is designed to return the relative position (such as 1, 2, or 3) of an item in a list.

If you use an INDEX formula that refers to ranges on separate worksheets, Excel displays the ____ error message.

Applying Fundamental Excel Skills and Tools in Problem Solving

Solving Problems with Statistical Analysis Tools

Determining Effective Data Display with Charts

Applying Logic in Decision Making

Evaluating the Financial Impact of Loans and Investments

Organizing Data for Effective Analysis

Using Data Tables and Excel Scenarios for What-If Analysis

Enhancing Decision Making with Solver

Troubleshooting Workbooks and Automating Excel Applications

Filters

Exam 5: Retrieving Data for Computation, Analysis, and Reference

In a FALSE VLOOKUP, if an exact match is not found, ____ is returned.

With the INDEX function, if you are using a noncontiguous range, you must enclose the entire reference in ____.

The syntax of the HLOOKUP function is as follows: HLOOKUP(lookup_value,table_array,row_index_num,range_lookup).

The ____________________ function returns the relative position of an item in a list, and can be used instead of LOOKUP, VLOOKUP, or HLOOKUP when you need the position of an item in a range instead of the item itself.

The ISBLANK function tests for a(n) ____.

MATCHING Identify the letter of the choice that best matches the definition of the word or phrase. -Allows you to retrieve data from multidimensional tables

When using an INDEX function, if you enter a row_num, column_num, or area_num outside of the range you referenced in the first argument, Excel displays ____.

MATCHING Identify the letter of the choice that best matches the definition of the word or phrase. -A lookup table in which the data to be searched is organized in rows

In a(n) two-dimensional table, Excel can search a row or column to find key data, and then use that data to locate the correct value. _________________________

The IS function called ISLOGICAL is for values that refer to a logical value. _________________________

When solving a(n) ____________________ formula, Excel looks up a value by testing for a criterion across a row, instead of down a column.

The syntax of the INDEX function is as follows: INDEX(value,row_num,column_num,area_num). _________________________

Mark is in charge of determining the unit pricing for each order for robot toys. Please refer to the chart above as you answer the questions below. -Mark tests the following formula with a FALSE type in the worksheet shown above: =VLOOKUP(D14,A$3:B$8,2,FALSE). The result is ____.

The function named IFERROR combines aspects of the IF function and the ____ function.

The INDEX function allows you to retrieve data from multidimensional tables.

With ____________________, you can specify your own text (or even a blank cell) when an error is encountered rather than the standard Excel message.

In a LOOKUP function, the argument ____ refers to the location of the data you want to look up.

The ____ function is designed to return the relative position (such as 1, 2, or 3) of an item in a list.

If you use an INDEX formula that refers to ranges on separate worksheets, Excel displays the ____ error message.

Applying Fundamental Excel Skills and Tools in Problem Solving

Solving Problems with Statistical Analysis Tools

Determining Effective Data Display with Charts

Applying Logic in Decision Making

Evaluating the Financial Impact of Loans and Investments

Organizing Data for Effective Analysis

Using Data Tables and Excel Scenarios for What-If Analysis

Enhancing Decision Making with Solver

Troubleshooting Workbooks and Automating Excel Applications

Filters