Chapter 5 - Retrieving Data for Computation, Analysis, and Reference Flashcards

1
Q

What contain data that can be used to create worksheets that list items and then perform calculations?

A

Lookup tables

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

What is the most effective and flexible way to retrieve data organized in columns?

A

The VLOOKUP function

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

Which function searches a specified part of a worksheet for data starting with the first column?

A

The VLOOKUP function

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

What is the format of the VLOOKUP function?

A

=VLOOKUP(lookup_value,table_array, col_index_num,range_lookup)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

True or False:

You don’t have to specify the col_index_num in the VLOOKUP function

A

False

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

Which argument represents the data that you want to look up?

A

Lookup_value

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

Which argument represents the range containing the data that you want to search to find the lookup_value?

A

Table_array

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

Which argument represents the number of the column containing the data you want to retrieve?

A

Col_index_num

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

Which argument represents the type of lookup you want to perform: TRUE or FALSE?

A

Range_lookup

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

True or False:

If using the lookup type FALSE, the VLOOKUP function looks only for an exact match of the lookup value

A

True

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

What does the VLOOKUP function display in a cell if an exact match isn’t found?

A

N/A

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

Which function looks up a value by testing for a criterion across a row?

A

The HLOOKUP function

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

What is the format of the HLOOKUP function?

A

=HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

Which argument represents the number of the row containing the data you want to retrieve?

A

Row_index_num

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

True or False:

The first row of the HLOOKUP table must be sorted in ascending order

A

True

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

What do you use when the first column of the lookup table contains the key data and information you want to retrieve is in a column to the right of the key data column?

A

The VLOOKUP function

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

What do you use when the first row of the lookup table contains the key data and information you want to retrieve is in a column to the right of the key data row?

A

The HLOOKUP function

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

What 2 functions should you use to retrieve data from multiple worksheets?

A

VLOOKUP and HLOOKUP

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

Which function looks up the greatest value that does not exceed a specified value anywhere in a table or range?

A

The LOOKUP function

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

What is the format of the LOOKUP function?

A

=LOOKUP(lookup_value,lookup_vector,result_vector)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

True or False:

The LOOKUP function can retrieve data from a lookup table with a vertical or horizontal orientation

A

True

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

Which argument represents the location of the data that you want to look up?

A

Lookup_vector

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

Which argument represents the location of the data that you want to retrieve?

A

Result_vector

24
Q

Which function should you use with two- or three-dimensional tables?

A

The INDEX function

25
Which function returns the value in a table based on the row and column numbers that you specify?
The INDEX function
26
What is the format of the INDEX function?
=INDEX(reference,row_num,column_num,area_num)
27
Which argument represents the range containing the data that you want to find?
Reference
28
Which argument represents the number of the row in the range referenced in the first agreement of the INDEX function?
Row_num
29
Which argument represents the number of the column in the range referenced in the first agreement of the INDEX function?
Column_num
30
Which argument represents the part of the nonadjacent range referenced in the first agreement of the INDEX function?
Area_num
31
What are often used in formulas to test the outcome of a calculation?
IS functions
32
What are the 9 functions that test a value or cell reference, and then return a TRUE or FALSE value depending on the results?
The IS functions
33
What do IS function help to locate when they are combined with the IF function?
Data-entry errors
34
What is the format of the IS function?
ISBLANK(value)
35
Which IS function checks whether a specified value refers to an empty cell?
ISBLANK
36
Which IS function checks whether a specified value refers to any error value except #N/A?
ISERR
37
Which IS function checks whether a specified value refers to any error value?
ISERROR
38
Which IS function checks whether a specified value refers to a logical value?
ISLOGICAL
39
Which IS function checks whether a specified value refers to the #N/A error value?
ISNA
40
Which IS function checks whether a specified value refers to any item that is not text?
ISNONTEXT
41
What does the ISNONTEXT function return if the value refers to a blank cell?
TRUE
42
Which IS function checks whether a specified value refers to a number?
ISNUMBER
43
Which IS function checks whether a specified value refers to a reference?
ISREF
44
Which IS function checks whether a specified value refers to text?
ISTEXT
45
What is a Reference and Lookup function that can return a value or a range for up to 254 different values?
The CHOOSE function
46
What is the format of the CHOOSE function?
=CHOOSE(index_num,value1,value2,…)
47
What is the format of the INDEX function?
=INDEX(reference,row_num,column_num,area_num)
48
Which function is designed to return the relative position of an item in a list?
The MATCH function
49
What is the format of the MATCH function?
=MATCH(lookup_value,lookup_array,match_type)
50
Which argument in the MATCH function represents the value you want to match in the list?
Lookup_value
51
Which argument in the MATCH function represents a one-dimensional horizontal or vertical list?
Lookup_array
52
What are the 3 match types?
1, 0, -1
53
What is the default match type?
Type 1
54
Which match type requires the function to find an exact match or display the error message #N/A?
Type 0
55
Which match type finds a match that returns the greatest value that is less than or equal to the lookup_value?
Type 1
56
Which match type finds a match that returns the greatest value that is greater than or equal to the lookup_value?
Type -1
57
What value does the ISBLANK function return?
TRUE or FALSE