• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL
Help

Part VIII: Appendixes > Lookup and Reference Functions

Lookup and Reference Functions

When you work with lists of data in Excel, particularly large lists, you often need to find information within those lists as part of a worksheet function. The Lookup and Reference functions perform this job in a variety of ways to meet these needs. Some of these functions return cell references when the information is found, while others return the contents of the found cell (or nearby cells) using different data types. Table B.6 shows you the lookup and reference functions available in Excel 97.

Table B.6. Lookup and Reference Functions in Excel
Function SyntaxReturns
=ADDRESS(row_num,column_num, abs_num,a1,sheet_text)Text for a given cell reference
=AREAS(reference)Number of contiguous areas of data in a given cell range
=CHOOSE(index_num,value1,value2,…)Value within a list based on an index
=COLUMN(reference)Column number of a cell reference
=COLUMNS(array)Number of columns in an array
=HLOOKUP(lookup_value,table_array, row_index_num,range_lookup),Value found in a cell using a horizontal (row-based) lookup in a table
=HYPERLINK(link_location,friendly_name)Shortcut or jump to a document
=INDEX(input,row_num,column_num,area_num)Value or cell reference from a table using an index value; use the area_num argument only in the cell reference form of this function
=INDIRECT(ref_text,a1)Cell reference based on a text string
=LOOKUP(lookup_value,array)Value from a single-dimension range (a one-row or one-column range)
=MATCH(lookup_value,lookup_array, match_type)Value indicating relative position of a matched item in an array
=OFFSET(reference,rows,cols,height,width)Cell reference that is indexed to a base position or range
=ROW(reference)Row number of a cell reference
=ROWS(array)Number of rows in an array
=TRANSPOSE(array)Range of cells that is transposed from a base range (row becomes column or vice versa)
=VLOOKUP(lookup_value,table_array, col_index_num,range_lookup)Value found in a cell using a vertical (column-based) lookup in a table



PREVIEW

                                                                          

Not a subscriber?

Start A Free Trial


  
  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint