Question 1

The table_array argument must contain ____ cell references so that the row values will not change when the formula is copied down the column.

Accepted Answer

Absolute cell references will ensure that the row values do not change when the formula is copied down the column. This is important if the table contains data that needs to remain fixed and not change with the formula's reference. Relative, simple and complex cell references do not have this feature.

Question 2

In an INDEX function, the argument ____ refers to the number of the row in the range referenced in the first argument.

Accepted Answer

The argument "row_num" refers to the number of the row in the range referenced in the first argument. The other options, "reference," "column_num," and "area_num," refer to different arguments in the INDEX function.

Question 3

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

Accepted Answer

The lookup_value argument in a LOOKUP function refers to the specific value that you want to look up in the lookup_vector or lookup_range.

Question 4

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

Accepted Answer

The INDEX function is versatile and can be used to retrieve data from both one-dimensional (single row or column) and multidimensional (multiple rows and columns) arrays or ranges in Excel, making it suitable for working with multidimensional tables.

Question 5

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

Accepted Answer

The result_vector argument in a LOOKUP function specifies the range from which the function retrieves the data corresponding to the lookup_value.

Question 6

The ____ function looks up the greatest value that does not exceed a specified value anywhere in a table or range.

Accepted Answer

The LOOKUP function is the best choice because it returns the closest match to the lookup value that does not exceed it. HLOOKUP and VLOOKUP are more specific functions that search for a value in a particular row or column.

Question 7

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

Accepted Answer

The lookup_vector is the argument that refers to the location of the data you want to look up in a LOOKUP function.

Question 8

You can only use a LOOKUP function when you want to retrieve a value that is stored ____ a key data row in a horizontal lookup table.

Accepted Answer

The LOOKUP function retrieves a value that is stored above a key data row in a horizontal lookup table.

Question 9

The syntax of the LOOKUP function is as follows: =LOOKUP(____,lookup_vector,result_vector).

Accepted Answer

The syntax of the LOOKUP function requires a lookup value as the first argument. This value is used to search for a match in the lookup vector.

Question 10

The LOOKUP function uses a ____ lookup type, so the column or row containing the lookup values must be in ascending order.

Accepted Answer

The LOOKUP function uses an approximate lookup type, which requires that the column or row containing the lookup values be in ascending order. If the values are not in ascending order, the function may return incorrect results. Therefore, option A is the correct answer.

Question 11

A ____ at the beginning of the formula indicates that Excel should display the results as a negative value.

Accepted Answer

A negative sign (-) at the beginning of the formula indicates that Excel should display the results as a negative value. This is a common way to represent values such as expenses or losses. The other options are not relevant to this feature in Excel.

Question 12

As with the VLOOKUP and HLOOKUP functions with the TRUE lookup type, the ____ function looks for a value that matches the criterion by comparing values based on their position in the lookup table.

Accepted Answer

The lookup function looks for a value that matches the criterion by comparing values based on their position in the lookup table, just like the VLOOKUP and HLOOKUP functions with the TRUE lookup type. The other options (A, C, and D) are not relevant or do not exist in this context.

Question 13

You can only use a LOOKUP function when you want to retrieve a value that is stored to the ____ of a key data column in a vertical lookup table.

Accepted Answer

The answer of You can only use a LOOKUP function...

Question 14

If the values you want to look up are in column B and the values you want to retrieve are in column A, you can use the ____ function.

Accepted Answer

The LOOKUP function can be used to retrieve values from a column to the left of the lookup column, which is not possible with VLOOKUP.

Question 15

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

Accepted Answer

The answer of In a(n) ____ table, Excel can search...

Question 16

You can use VLOOKUP and HLOOKUP to retrieve data stored in lookup tables on ____.

Accepted Answer

Both VLOOKUP and HLOOKUP can be used to retrieve data from lookup tables on the same worksheet or on other worksheets. Therefore, the correct answer is option C, "either a or b".

Question 17

If the lookup_value is less than the smallest value in the lookup_vector, LOOKUP displays ____.

Accepted Answer

If the lookup value is less than the smallest value in the lookup vector, LOOKUP displays an error symbol since there is no value to look up.

Question 18

The LOOKUP function has the ____ lookup type.

Accepted Answer

The LOOKUP function has the TRUE lookup type, which means it performs an approximate match instead of an exact match. If the lookup value is not found, it will return the next smaller value.

Question 19

In an INDEX function, the argument ____ refers to the range containing the data you want to find.

Accepted Answer

The argument "reference" in the INDEX function refers to the range containing the data you want to find. This is the first argument in the function syntax, and it typically includes a specific range or cell reference, such as A1:B10, or a named range, such as "SalesData". The other arguments in the INDEX function specify which row or column to return from the reference range, based on the "row_num" or "column_num" argument. The "area_num" argument is only used when the reference range consists of multiple non-adjacent areas, which is less common.

Question 20

In a(n) ____ table, Excel searches one dimension, such as the columns, and then searches another dimension, such as the rows, to find the value at the intersection of a single row and column.

Accepted Answer

A two-dimensional table, also known as a matrix or grid, has both rows and columns and is used in Excel to search for a value at the intersection of a single row and column. The other options, one-dimensional and three-dimensional tables, do not fit this description.

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