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