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

Question 1

The INDEX function allows you to specify a list of nonadjacent ranges as the reference argument, and then specify which of these ranges to use in the area_num argument.

Answer 1

True

Question 2

When creating a vertical lookup table with the TRUE type, be certain to start with the lowest possible value, such as 0, so that the table covers all the possible data.

Answer 2

True

Question 3

When you use a lookup type of TRUE, the VLOOKUP function looks only for an exact match of the lookup value.

Answer 3

False

Question 4

When you write a VLOOKUP formula, you indicate the value you want to look up in a table.

Question 5

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

Question 6

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

Question 7

You can use a VLOOKUP formula and an HLOOKUP formula to retrieve data stored in lookup tables on the same worksheet.

Question 8

A vertical lookup table is a lookup table in which the data to be searched is organized in columns.

Question 9

Headings at the top of each column should be included as part of the lookup_range in the VLOOKUP formula.

Question 10

You can often use a LOOKUP function instead of a VLOOKUP or HLOOKUP function with a TRUE lookup type.

Question 11

Unlike VLOOKUP, the RLOOKUP function looks up the greatest value that does not exceed a specified value anywhere in a table or range.

Question 12

In a one-dimensional 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.

Question 13

When using a VLOOKUP function, it's not always obvious when a returned value is incorrect.

Question 14

LOOKUP has both a TRUE and a FALSE lookup type.

Question 15

Range names copy absolutely.

Question 16

A data list that categorizes values you want to retrieve is called a linkup table.

Question 17

The rules for an HLOOKUP function with a TRUE lookup type test the values down the first column.

Question 18

If a lookup_value is smaller than the smallest value in the first column of table_array, VLOOKUP opens up the Lookup Wizard.

Question 19

You cannot use VLOOKUP, HLOOKUP, or LOOKUP functions with a two-dimensional table.

Question 20

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

Question 21

In VLOOKUP, if a type FALSE is specified, an exact or approximate match is returned. _________________________

Question 22

The data you are looking up is also called the ____ data.

A) key
B) look
C) important
D) clean

Question 23

When you use the HLOOKUP function, you specify the row_index_num. _________________________

Question 24

VLOOKUP searches for a matching value in the leftmost column of the table, and then retrieves the value ____.

A) in the same row but in another column that you specify
B) in the same column but in another row that you specify
C) anywhere in the table
D) in the same row, in the very next column

Question 25

The V in VLOOKUP stands for velocity. _________________________

Question 26

A data list that categorizes values you want to retrieve is called a ____ table.

A) lookup
B) list
C) query
D) help

Question 27

Excel provides nine functions, called the OF functions, that test a value or cell reference, and then return a TRUE or FALSE value depending on the results. _________________________

Question 28

The syntax of the CHOOSE function is as follows: =CHOOSE(index_num,value1,value2,…). _________________________

Question 29

Internal values are those pieces of data that you know, such as customer names, quantities, or product descriptions. _________________________

Question 30

A function named IFWRONG can be used in combination with the VLOOKUP function to simplify error checking. _________________________

Question 31

You use VLOOKUP when ____ of the lookup table contains the data you are looking up.

A) the first column
B) the last column
C) any column
D) all columns

Question 32

The final argument of the VLOOKUP function is the range_lookup type, which can be TRUE or FALSE. _________________________

Question 33

The Lookup function argument called lookup_vector is the location of the data you want to look up. _________________________

Question 34

If you want to use the INDEX function to retrieve data stored in more than one range, be certain to store all the lookup ranges on the same computer. _________________________

Question 35

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

Question 36

When you write a VLOOKUP formula, you indicate the value you want to look up in a ____.

A) table
B) chart
C) graph
D) list

Question 37

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. _________________________

Question 38

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

Question 39

You can use the ISEMPTY function to test a cell reference. _________________________

Question 40

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

Question 41

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

A) =-HLOOKUP(F14,I$7:K$9,3,TRUE)*H14
B) =(NEGATIVE)HLOOKUP(F14,I$7:K$9,3,TRUE)*H14
C) =(0)HLOOKUP(F14,I$7:K$9,3,TRUE)*H14
D) none of the above

Question 42

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

A) lookup_range
B) range_lookup
C) row_index_num
D) table_value

Question 43

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

A) row
B) column
C) table
D) worksheet

Question 44

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

A) ascending
B) descending
C) alphabetical
D) numerical

Question 45

The H in HLOOKUP stands for ____.

A) help
B) horizontal
C) historic
D) hit

Question 46

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

A) FALSE
B) TRUE
C) complex
D) simple

Question 47

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

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

Question 48

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.

A) Sort
B) Order
C) Range
D) Data

Question 49

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.

A) descending
B) ascending
C) alphabetical
D) numerical

Question 50

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

A) FALSE
B) TRUE
C) complex
D) simple

Question 51

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

A) lookup_value
B) table_array
C) col_index_num
D) range_lookup

Question 52

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

A) one
B) two
C) three
D) four or more

Question 53

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

A) the last column
B) any column
C) the first column
D) the first row

Question 54

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

A) table_index
B) table_array
C) table_range
D) table_row

Question 55

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

A) lookup_value
B) table_array
C) col_index_num
D) range_lookup

Question 56

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

A) lookup_value
B) table_array
C) col_index_num
D) range_lookup

Question 57

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

A) lookup_value
B) table_array
C) col_index_num
D) range_lookup

Question 58

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

A) #N/A
B) ##
C) #ERROR#
D) @#@#

Question 59

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.

A) descending
B) ascending
C) alphabetical
D) any (values do not need to be sorted)

Question 60

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.

A) columns
B) rows
C) either a or b
D) neither a nor b

Question 61

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

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

Question 62

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

A) HLOOKUP
B) VLOOKUP
C) LOOKUP
D) all of the above

Question 63

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

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

Question 64

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

A) reference
B) row_num
C) column_num
D) area_num

Question 65

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

A) TRUE
B) FALSE
C) both a and b
D) neither a nor b

Question 66

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

A) INDEX
B) LOOKUP
C) VLOOKUP
D) HLOOKUP

Question 67

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

A) an error symbol
B) the LOOKUP wizard
C) the number 0
D) the number 100

Question 68

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.

A) left
B) right
C) either a or b
D) neither a nor b

Question 69

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.

A) above
B) below
C) either a or b
D) neither a nor b

Question 70

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.

A) POSITION
B) LOOKUP
C) LINK
D) LIST

Question 71

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

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

Question 72

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.

A) one-dimensional
B) two-dimensional
C) three-dimensional
D) none of the above

Question 73

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

A) absolute
B) relative
C) simple
D) complex

Question 74

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

A) negative sign
B) double negative sign
C) (0)
D) none of the above

Question 75

The LOOKUP function has the ____ lookup type.

A) TRUE
B) FALSE
C) either a or b
D) neither a nor b

Question 76

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.

A) one-dimensional
B) two-dimensional
C) three-dimensional
D) none of the above

Question 77

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.

A) VLOOKUP
B) LOOKUP
C) either a or b
D) neither a nor b

Question 78

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

A) lookup_value
B) lookup_reference
C) lookup_range
D) lookup_index

Question 79

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

A) reference
B) row_num
C) column_num
D) area_num

Question 80

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

A) the same worksheet
B) other worksheets
C) either a or b
D) neither a nor b