Lookup Formulas: Vlookup, Hlookup, Index & 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

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

INDEX()

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

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