Question 1

In a VLOOKUP function, the argument ____ refers to the number of the column containing the data you want to retrieve.

Accepted Answer

The argument 'col_index_num' in a VLOOKUP function refers to the number of the column containing the data you want to retrieve.

Question 2

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

Accepted Answer

In a FALSE VLOOKUP, if an exact match is not found, an error value (#N/A) is returned. This is because the "FALSE" parameter in the formula specifies that only an exact match should be returned.

Question 3

To return a negative value, ____ is a correct formula.

Accepted Answer

The formula begins with a negative sign (-) before the HLOOKUP function, which will return a negative value.

Question 4

When creating a range for use with a type FALSE VLOOKUP table, make sure to sort the values in the first column of table_array in ____ order.

Accepted Answer

The answer of When creating a range for use with...

Question 5

A VLOOKUP table can have ____ key value(s) in each cell of its first column.

Accepted Answer

A VLOOKUP table can have only one key value in each cell of its first column. This key value is used to match against the lookup value and retrieve the corresponding data in the subsequent columns.

Question 6

When solving an HLOOKUP formula, Excel looks up a value by testing for a criterion across a ____.

Accepted Answer

In an HLOOKUP formula, Excel searches for a criterion across a row, not a column, table, or worksheet. The formula returns a value from a specified cell in the same column as the search criterion.

Question 7

To find and retrieve data from a vertical lookup table in the most efficient way, create a simple worksheet that contains related data, and organize this data in ____ to form the vertical lookup table.

Accepted Answer

To create a vertical lookup table, the data needs to be organized in columns. The first column should contain the lookup values, and the columns to the right should contain the related data. This allows for easy use of the VLOOKUP function, which requires the lookup value to be in the first column.

Question 8

In a VLOOKUP function, the argument ____ refers to the range containing the data that you want to search to find the lookup value.

Accepted Answer

The argument "table_array" refers to the range containing the data that you want to search to find the lookup value. This is where VLOOKUP will look for the value you specify in the first argument.

Question 9

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

Accepted Answer

The lookup_value argument in a VLOOKUP function refers to the data that you want to search for and retrieve information from the table. It is the first argument in the function and is required. The other arguments (table_array, col_index_num, range_lookup) depend on this lookup value to function correctly.

Question 10

If a lookup_value is smaller than the smallest value in the first column of table_array, VLOOKUP returns the ____ error value.

Accepted Answer

When the lookup_value is smaller than the smallest value in the first column of table_array, VLOOKUP function returns the #N/A error value. This is because the lookup value cannot be found in the table and therefore, there is no matching value to return.

Question 11

With a ____ type of VLOOKUP, the function returns the greatest value that does not exceed the lookup value.

Accepted Answer

This type of VLOOKUP is known as an approximate match, as it searches for the closest match to the lookup value rather than an exact match. By using TRUE as the fourth argument in the VLOOKUP formula, the function will return the greatest value that does not exceed the lookup value.

Question 12

In a VLOOKUP function, the argument ____ refers to the type of lookup you want to perform-TRUE or FALSE.

Accepted Answer

The argument "range_lookup" in a VLOOKUP function denotes whether you want to perform an approximate match (TRUE) or an exact match (FALSE).

Question 13

The first row (key values) of the lookup table must be sorted in ____ order to use an HLOOKUP function with a TRUE lookup type.

Accepted Answer

The first row (key values) of the lookup table must be sorted in ascending order to use an HLOOKUP function with a TRUE lookup type. This means the values need to be arranged from smallest to largest in order for the function to work correctly.

Question 14

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

Accepted Answer

The correct syntax for the HLOOKUP function includes a table_array parameter, which is the range of cells that contains both the lookup value and the corresponding results. This is often referred to as the lookup table or data table. Option B, "table_array," is the parameter name that corresponds to this requirement.

Question 15

The syntax of the VLOOKUP function is as follows: =VLOOKUP(lookup_value,table_array,col_index_num,____).

Accepted Answer

The last argument of the VLOOKUP function is range_lookup which determines whether you want an exact match or an approximate match. It should be set to TRUE or 1 for an approximate match and FALSE or 0 for an exact match.

Question 16

For a VLOOKUP table, list the key values in ____.

Accepted Answer

The key values (or lookup values) should be listed in the first column of the VLOOKUP table. This is because the VLOOKUP function searches for the lookup value in the first column of the table and returns the corresponding value in the same row from the column specified in the function. Therefore, the first column must contain the values that are used to search for the corresponding values.

Question 17

When creating a range for use with a type TRUE VLOOKUP table, make sure to sort the values in the first column of table_array in ____ order.

Accepted Answer

The values in the first column of the table_array must be sorted in ascending order when creating a range for use with a type TRUE VLOOKUP table. This ensures that VLOOKUP can find an approximate match for the lookup value, which is necessary when using the TRUE argument. If the first column is not sorted, VLOOKUP may return incorrect or unexpected results.

Question 18

The H in HLOOKUP stands for ____.

Accepted Answer

The H in HLOOKUP stands for horizontal, which means that the function is used to search for a value in the top row of a table and return the corresponding value in the same column from a specified row.

Question 19

If you have a valid range that is not in ascending order, you can sort it using the ____ commands in the Editing group on the HOME tab on the ribbon.

Accepted Answer

The "Sort" commands in the Editing group on the HOME tab on the ribbon allow you to sort a range in ascending or descending order based on a specified column or columns. This is the best choice for sorting a range that is not in ascending order.

Question 20

With a ____ type of VLOOKUP, the function would look only for the exact match.

Accepted Answer

In VLOOKUP function usage, specifying FALSE as the range_lookup argument forces the function to look for an exact match.

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