100 likes | 203 Views
Computer Science & Engineering 2111. Lecture 5 Reference Functions. VLOOKUP. Finds an entry from a vertical array based on a criteria =VLOOKUP( lookup_value,table_array,col_index_num ,[ range_lookup ]) lookup_value : criteria to lookup or “match”
E N D
Computer Science & Engineering 2111 Lecture 5 Reference Functions CSE 2111 Lecture 5-Reference Functions
VLOOKUP Finds an entry from a vertical array based on a criteria =VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup]) • lookup_value: criteria to lookup or “match” • table_array: the range or boundary of your table(excluding headings) • col_index_num: the column number in your range that contains the corresponding data • range_lookup: • True: Finds the exact match or the next lower value in your table array • False: Finds an exact match in your table array CSE 2111 Lecture 5-Reference Functions
Finds an exact match or Finds the lowest value without going over CSE 2111 Lecture 5-Reference Functions
TABLE RULES TRUE • Leftmost column should contain the table range • Rightmost column should contain the value • Leftmost column must be in ascending order • Finds in your table array an exact match or Finds the lowest value without going over • Beginning value in table array must be the lowest value of a lookup value. FALSE • Leftmost column should contain the table range • Rightmost column should contain the value • Finds the exact match in your table array CSE 2111 Lecture 5-Reference Functions
IFERROR Returns a value you specify if a formula evaluates to an error, otherwise it returns the result of the formula =IFERROR(value,value_if_error) CSE 2111 Lecture 5-Reference Functions
=IF(VLOOKUP(C8,A$3:C$5,2,FALSE)*B8 > VLOOKUP(C8,A$3$C$5,3,FALSE), VLOOKUP(C8$A$3:C$5,3,FALSE), (VLOOKUP(C8$A$3$C$5,2,FALSE)*B8)) CSE 2111 Lecture 5-Reference Functions