1 / 9

Using the VLookup Function

Using the VLookup Function. Use a cell value as a KEY to a table to find and return a specific element to another cell or use in a calculation. Lookup Tables and Functions. Lookup Tables and Functions.

fruma
Download Presentation

Using the VLookup Function

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. Using the VLookup Function Use a cell value as a KEY to a table to find and return a specific element to another cell or use in a calculation.

  2. Lookup Tables and Functions

  3. Lookup Tables and Functions • A lookup table is a table that organizes data based on different categories, in order to retrieve a value from the data • The category for the lookup table, called compare values or Key is located in the first column (VLookUp) or row (HLookUp) of the table. • To retrieve a particular value from the lookup table, a lookup value (the value you are trying to find) needs to match one of the compare values

  4. Lookup Table ExampleFortune Cookie Sentence Generator

  5. Lookup Table The Actual values and cell locations used in the example are different than what is shown here.

  6. Lookup Tables and Functions • Two types of lookup functions • VLOOKUP • VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) • HLOOKUP • HLOOKUP(lookup_value, table_array, row_index_num, [range lookup] • range_lookup: Optional - True or False. • If Blank or TRUE, it looks for an Approximate Match – KEYS MUST BE IN ASCENDING SEQUENCE • If the KEY is not found, it returns the value of the prior key • If FALSE, The Key must be an Exact Match and the Keys DO NOT need to be in order..

  7. Lookup Tables and Functions • Lookup functions can be used to find exact matches and approximate matches • Exact match looks for an exact match between the lookup value and a corresponding value in the lookup table • Approximate match looks for a correlation between the lookup value and a range of values

  8. Lookup Tables and Functions • When using an approximate match lookup (TRUE), the compare value (Key) in a lookup table must be sorted by alphabetical order (if text) or low-to-high order (if numeric) • Excel searches the first column (vlookup) or row (Hlookup) of the lookup table (compare values) until it locates the largest value that is still less than the key.

  9. Exercise VlookUpPayroll Record Part-3 • Use the VLOOKUP function to find an exact match for a code. • Vlookup is better than a Nested If when you have many possible values. • Management is so happy with the work you have done on the Payroll Record spreadsheet, they want to add a lookup to an Employee Information Data Table. • In addition, since the Employee name is used in other processes, they want to use a table that uses the ID to extract the Employee Name, Department , Pay Rate, Deduction code and Health Insurance Amount. The Employee Table will also contain another lookup to a Health Insurance Code Value Table.

More Related