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

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
FunctionSyntaxDescription
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,...)Uses the index number to return a value from a list of 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 pecified value in an array based on a table’s top row.
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 scell 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 (ArrayForm)=LOOKUP(lookup_value,array)Looks 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)Returns 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 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)Looks for a value in the leftmost column of a table and returns a value from the column number you specify.

PREVIEW

Not a subscriber?

Start A Free Trial

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