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

Chapter 13. Lookup and Reference Functio... > Lookup and Reference Functions Overv...

Lookup and Reference Functions Overview

Lookup and reference functions are probably some of the most frequently asked, “How do I?” questions of all functions. Because so many people use Excel to manage data stored in lists in some form or fashion, at times you'll need to look up information associated with an item. For example, if you have several product names in a database and want to look up the associated inventory number for the product, you could do this several ways using Excel's lookup functions. You could, for example, use the VLOOKUP function or combine the INDEX and MATCH functions to pinpoint the inventory number. This chapter covers the functions in Table 13.1 that appear in bold font.

Table 13.1. Lookup and Reference Functions
Function Syntax Description
ADDRESS =ADDRESS(row_num, column_num,abs_num,A1, sheet_text) Returns the cell address in relative and absolute form. Returns the actual location of the cell on the spreadsheet.
AREAS =AREAS(reference) Returns the number of areas in a reference. An area is a range of contiguous cells or a single cell.
CHOOSE =CHOOSE(index_num, value1,value2,…) Returns the index number from a list based on up to 29 choice arguments.
COLUMN =COLUMN(reference) Returns the relative column number based on a given reference.
COLUMNS =COLUMNS(array) Returns the number of columns in an array or reference.
HLOOKUP =HLOOKUP(lookup_value,table_array, row_index_number,range_lookup) Searches for a specified value in an array based on a table's top row.
HYPERLINK =HYPERLINK (link_location,cell_contents) Creates a shortcut to jump to a document stored within a workbook or on a network server. You can also use the hyperlink function to link to a URL, such as a stock page on the Web.
INDEX(Array Form) =INDEX(array,row_num, column_num) Returns the value of an element selected by the row number and column letter indexes within a table or array.
INDEX (Reference Form) =INDEX(reference, row_num,column_num, area_num) Returns the reference of the cell based on the intersection of a particular row and column within a table or array.
INDIRECT =INDIRECT(ref_text,A1) Returns the reference based on a text string.
LOOKUP(Array Form) =LOOKUP(lookup_value, array) in the first row or column of an array, and returns the specified value from the same position in the last row or column of the array.
LOOKUP (Vector Form) =LOOKUP(lookup_value, lookup_vector, result_vector) the value from the same position in a second one-row or one- column range based on a lookup value located in the first one-row or one-column range.
MATCH =MATCH(lookup_value, lookup_array,match_type) Returns the position of an item in an array that matches a specified value in a specified order.
OFFSET =OFFSET(reference,rows, columns,height,width) Returns a reference to a range that is a specific number of rows and columns from a cell or range of cells.
ROW =ROW(reference) Returns the row number based on a reference.
ROWS =ROWS(array) Returns the number of rows in an array or reference.
TRANSPOSE =TRANSPOSE(array) Returns a horizontal range of cells as vertical or vice versa.
VLOOKUP =VLOOKUP(lookup_value, table_array, column_index_num, range_lookup) for a value in the leftmost column of a table and returns a value from the column number you specify.



Not a subscriber?

Start A Free Trial

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