Microsoft Office Tutorials and References
In Depth Information
Based on an array or reference, COLUMNS returns the number of columns.
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
HLOOKUP searches for a specified value in an array or a table’s top row.
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
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.
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.