1 / 15

Table LookUp

Table LookUp. Data Table (File, List). A collection of records and each record contains fields. Lookup example: Given EmpID, retrieve salary. Data Table Example. Decision Table Example. Decision Table Example. VLookUp Function. VLookUp( SearchValue, TableRange, TableColumn# )

tragland
Télécharger la présentation

Table LookUp

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. Table LookUp

  2. Data Table (File, List) • A collection of records and each record contains fields. • Lookup example: • Given EmpID, retrieve salary

  3. Data Table Example

  4. Decision Table Example

  5. Decision Table Example

  6. VLookUp Function • VLookUp(SearchValue, TableRange, TableColumn#) • Table Range: A6:C8 *** Heading is not included • Column number: Each column is assigned a number: • First column is 1, second column is 2, etc. • The first column is called Search Column and values in the first column must be in ascending order. • Search Value: Value used to search the first column to determine the row of the table. • To read the price: • =VLookUp(B1,A6:C8,3)

  7. Absolute Reference to a Table Note: To name a range: Select the cell/range; then right click and choose: Define name

  8. Range Look Up • Discount Table: • Total Sales • At least But less than Discount Rate • 0 300 0% • 300 500 3% • 500 1000 5% • 1000 7%

  9. How to determine the row? • The function locates the first value in column that exceeds the search value, then it backs up one row. Search value greater than or equal to the last value, uses the last row. Is the “But less than” column needed?

  10. Tax Table

  11. Table Design • Professor’s rules for letter grades: • Avg >= 90 A • 90 > Avg >= 80 B • 80 > Avg >= 70 C • 70 > Avg >= 60 D • 60 > Avg F

  12. Exact Match Look Up . What if P4 is entered in B1? . To do an Exact Match look up: VLookUp(SearchValue, TableRange, TableColumn#, False)

  13. Practice

  14. Other Look up Functions • Match: Returns the relative position of an item in an range that matches a specified value in a specified order. • Use MATCH instead of one of the LOOKUP functions when you need the position of an item in a range instead of the item itself. • Index: Returns the reference of the cell at the intersection of a particular row and column.

  15. Example • Mileage Chart:

More Related