Excel Functions

Click on a link to see the associated Excel functions

• Intro
• Numeric
• String/Text
• Boolean
• Date and Time
• Table
• Level 1
• Level 1 and 2
• All

Table Functions

HLOOKUP

• Category: table
• User level: 3
• Syntax: =HLOOKUP(lookup value, table, offset, [optional] match type)
• Example: =HLOOKUP("foo", B1:U4, 2, TRUE)

This function ("horizontal lookup") searches a table to find a specific column, and then return a value in that column. Essentially, it works like an inverted WHERE clause in SQL, but searching columns instead of rows; it is the inverse function of VLOOKUP, which is closer to the actual WHERE clause.

The first parameter is the value to find in the table. The second parameter is the range of the table, with the top row containing the lookup value to match. The third column is row offset, meaning the number of rows below the first (lookup) row. The fourth parameter is optional and gives controls whether a "close match" is returned if an exact match isn't found; set this to TRUE to return an exact match, and "N/A" if the value is not found in the lookup column; the default value is FALSE, and it returns a "close match". I generally recommend using the exact match.

The function VLOOKUP works essentially the same way, but it searches "vertically" down a column for a value.

INDEX

• Category: table
• User level: 3
• Syntax: =INDEX(range, row offset, column number)
• Example: =INDEX(C3:T10, 4, 2)

This function returns the value in a table at the specified row and column specified by the row offset (the number of rows under the header) and column number (starting at one).

This function is confusing because it breaks a standard programming convention of staying consistent with references, that is, it uses the row OFFSET but the column INDEX. This means to obtain the value in the upper-left corner of the table, you would use =INDEX(C3:T10, 1, 1)

MATCH

• Category: table
• User level: 3
• Syntax: =MATCH(lookup value, table range, [optional] match type )
• Example: =MATCH("foo", B1:B10, 1)

This function returns the position of a matching value in a table column, the position being the row offset from the table header (not the actual row number in the spreadsheet).

Note that this function is dependent on the sort order, and will return the position of the first matching value it finds.

The third parameter controls where it uses an exact match (0), the first value less than or equal to the lookup value (1), or the first value greater than or equal to the lookup value (-1).

VLOOKUP

• Category: table
• User level: 3
• Syntax: =VLOOKUP(matching value, table range, table column, [optional] match type)
• Example: =VLOOKUP("foo", B1:F20, 3, TRUE)

This function ("vertical lookup") searches a table to find a specific row, and then return a value in that row. Essentially, it works like the WHERE clause in SQL, but the syntax is relatively confusing.

The first parameter is the value to find in the table. The second parameter is the range of the table, with the first (leftmost) column containing the lookup value to match. The third column is column offset, meaning the number of columns to the right of the first (lookup) column. The fourth parameter is optional and gives controls whether a "close match" is returned if an exact match isn't found; set this to TRUE to return an exact match, and "N/A" if the value is not found in the lookup column; the default value is FALSE, and it returns a "close match". I generally recommend using the exact match.

The function HLOOKUP works essentially the same way, but it searches "horizontally" across a row for a value.