1 / 10

Computer Science & Engineering 2111

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”

lysa
Download Presentation

Computer Science & Engineering 2111

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Computer Science & Engineering 2111 Lecture 5 Reference Functions CSE 2111 Lecture 5-Reference Functions

  2. 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

  3. Finds an exact match or Finds the lowest value without going over CSE 2111 Lecture 5-Reference Functions

  4. 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

  5. CSE 2111 Lecture 5-Reference Functions

  6. 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

  7. CSE 2111 Lecture 5-Reference Functions

  8. CSE 2111 Lecture 5-Reference Functions

  9. =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

  10. CSE 2111 Lecture 5-Reference Functions

More Related