Lookup Formulas: Vlookup, Hlookup, Index & Match

finding a topic in a textbook using the index

lookup functions are similar to what?

match type -1

match that is greater than but not equal

match type 1

match that is less than but not equal

combining match and index

result returned from match formula becomes argument for the row in index function

MATCH()

returns the relative position of a value within a list

vlookup true match

-approximate or close match -use when matching between two values -must be in ascending order

index and match for 2D searches

-returns a value at intersection of two values -use match twice, once for row num and once for column num

what lookup formulas do

-search in a table for a specified value -return a value related to the specified value

data validation

adds a drop-down box that limits the options you can choose to what is available in the specified range

INDEX() formula

arguments = array,row_num,column_num -array: list of values to return a value from -row num: row to return value from -col num: column to return value from (use if list is horizontal)

MATCH() formula

arguments = lookup_value,lookup_array,match_type -lookup value: value you want to find in array -lookup array: list of values you want to search in (single row or column!) -match type: type of match

VLOOKUP()/HLOOKUP() formula

arguments = lookup_value,table_array,col_index_num,range_lookup -lookup value: cell you want to match on -table array: highlight table/range to look in -col index num: column # from where you want to return value -range lookup: what kid of match

match type 0

exact match

vlookup false match

exact match

HLOOKUP()

looks for a value in a range of columns and returns a value below

VLOOKUP()

looks for a value in a range of rows and returns value to the right

INDEX()

returns a value from a specified position from within a table or range