Microsoft Office Tutorials and References

In Depth Information

**COLUMNS**

COLUMNS

Based on an array or reference,
COLUMNS
returns the number of columns.

=COLUMNS(array)

Similar to the
COLUMN
function, however, it returns the number of columns in an array or

reference. If you have several columns and you want a quick reference as to how many

columns are being used, you can use the
COLUMNS
function. For example:

This is the array formula or reference to a range of cells that will return

the number of columns within the array.

=COLUMNS(B5)
results in 1.

=COLUMNS(B5:D5)
results in 3, and so on. It simply adds up the number of

columns referenced.

ARRAY

HLOOKUP

HLOOKUP
searches for a specified value in an array or a table’s top row.

=HLOOKUP(lookup_value,table_array,row_index_number,range_lookup)

The
HLOOKUP
function searches for a column heading that is defined in the table array and

then it returns a number associated with the row index.

You can use text reference for the lookup value as shown in the first example in Figure 9.3. Using

text for the lookup value produces the following formula and result:
=HLOOKUP(“Q2”,D5:G9,2)

results in $65,087. The quarter specifies the column heading, the table array includes the entire

data table and the headings, and 2 is the number of rows indexed down. The range lookup

argument is not necessar y in this example.

The second example is a more efficient way to use the
HLOOKUP
function. The reason for this is

that the formula acts upon the values input in the cells to the left. With cell referencing your

formula becomes a live working model. Now you can apply different region numbers for index

values as well as column headings, and the function will return the index and column heading

result.

If you look at the second example in Figure 9.3, you’ll see that
=HLOOKUP(B8,D5:G9,C18)
results

in $33,929. The quarter reference for the lookup value is Q3, the table array includes the column

heading, and the index value is referenced to the number of rows down the left side.

This is the value to be looked up in the first row of the table. This

can be values, references, and text strings.

LOOKUP_VALUE

The table or range in which you are looking up information. You can

also use references to names or range names for the table array.

TABLE_ARRAY