Site hosted by Angelfire.com: Build your free website today!

Lookup Functions

Function Basics Insert Function AutoSum IF Function
PMT Function Date Functions DATEDIF Function Lookup Functions
New 07 Functions Time Functions Text Functions Rounding Functions
Stat Functions Other Functions SUBTOTAL (.xls) SUMPRODUCT (.xls)

There are a number of functions in Excel that allow you to search for information in a table of data. Here are the two most used lookup functions.

HLOOKUP
Searches for a value in the top row of a table of values, and then returns a value in the same column from a row you specify.
Syntax: HLOOKUP(lookup value, table, row num, range lookup)
Lookup value is the value to find in the first row.
Table is the data table to use for the lookup.
Row num is the row index number from which to return matching data.
Range lookup is a logical value: TRUE (or 1) or omitted for an approximate match or FALSE (or 0) for an exact match.  If True the values in the first row must appear in ascending order.
hlookup

VLOOKUP
Searches for a value in the first column of a table of values, and then returns a value in the same row from a column you specify.
VLOOKUP(lookup value, table, col num, range lookup)
Lookup value is the value to find in the first column.
Table is the data table to use for the lookup.
Col num is the column index number from which to return data.
Range lookup is a logical value: TRUE (or 1) or omitted for an approximate match or FALSE (or 0) for an exact match.  If True the values in the first row must appear in ascending order.
vlookup

Download the sample workbook functionsamples.xls or for 2007 users functionsamples07.xls.

Return to the Top