Tutorial 7

You implement the VLOOKUP formula at your colleague's suggestion, but you get the #VALUE! error as the result of the formula. Which of the following is likely true?

You might have entered a table name for the table_array value instead of the cell reference of the lookup table. xxx You might have indicated that the compare values are a range of values instead of an exact match. You might have entered a number for the col_index_num instead of a letter. You might have entered a letter for the col_index_num instead of a number.

If you are creating a calculated column or formula within an Excel table, you can use the ____ structured reference in the formula.

basic advanced unqualified <<< restricted

When Excel automatically fills the rest of a table column with a formula entered into an empty table column, this is referred to as a(n) ____ column.

concatenated calculated <<<<< autofilled custom

Error values indicate that some element in a ____ is preventing Excel from returning a calculated value.

formula cell referenced in a formula either a. or b. <<< neither a. nor b.

When you create a calculated column, you can use ____ references to create the formula.

indexed structured <<< key logical

Using the VLOOKUP function, when the lookup value matches a(n) ____ value, the corresponding value from the lookup table is returned to the cell with the lookup formula.

key x index compare rating

When you want to reference an entire column of data in a table, you create a column ____.

label name variable qualifier <<<

When using VLOOKUP, the ____argument is optional.

lookup_value table_array range_lookup <<< col_index

Placing cell references to input values rather than constants in formulas makes a worksheet ____.

more flexible easier to update both a. and b. <<< neither a. nor b.

The ____ function calculates the difference between two dates and shows the result in months, days, or years.

nested IF CALIF DATEDIF <<<< DATE

The COUNTIF function is sometimes referred to as a(n) ____ count.

network criterion conditional <<< basic

Omitting the range_lookup entry makes VLOOKUP a(n) ____ lookup.

range <<<< approximate match exact match calculated

The ____ error value occurs when an invalid value is specified in the LOOKUP function.

#NAME? #N/A <<< #NULL! #NUM!

For the col_index_num value, be sure to enter the number that corresponds to the column's position rather than its column letter; otherwise, the VLOOKUP formula may return ____.

#NAME? #VALUE! x either a. or b. <<<< neither a. nor b.

The ____ function is a logical function that returns a TRUE value if any of the logical conditions are true and a FALSE value if all the logical conditions are FALSE.

IF AND OR <<<<<< THIS

___ references make it easier to create formulas that use portions or all of an Excel table because the names or headers are usually simpler to identify than cell addresses.

Indexed Structured <<< Key Logical

If you are not sure of a table's name, click in the table, click the ____ tab on the Ribbon, and then click the Table Name box in the Properties group.

Layout Format Table Tools Design <<< Structured References

You could test two or more conditions and determine whether all conditions are true using the ____ function.

OR IF AND <<<< THIS

____ lookup tables are commonly used to find a taxpayer's tax rate in a tax table, find a shipping charge based on the weight of a package in a shipping charges table, or determine a student's letter grade from a table of grading criteria.

Range Approximate <<< Calculated Exact match

You can edit existing conditional formatting rules from the Conditional Formatting ____ dialog box. Click the Edit Rule button and make the appropriate changes.

Rules Editor Rules Manager <<< Editor Manager

If you omit the range_lookup value from a LOOKUP function, which of the following is true?

The function returns the #NAME? error value. Your function can only search the first column of the lookup table. It is considered an approximate match. <<< It is considered an exact match.

The ____ function searches vertically down the first column of the lookup table.

VLOOK VLOOKUP <<< HLOOK HLOOKUP

To get the effect of a calculated column in a range of data, you must copy and paste the formula or use the ____ feature.

Concatenation Merged AutoFill <<< Indexed