1 / 12

MS Excel Lookup Functions

MS Excel Lookup Functions. Southwest Florida Water Management District Data Analyst Interview. Mark Brady 11/19/2012. MS Excel Lookup Functions. MS Excel Lookup Functions. These are functions that operate on lists or arrays of data Data may reside in Workbooks, Databases, or text files

ryann
Download Presentation

MS Excel Lookup Functions

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. MS Excel Lookup Functions Southwest Florida Water Management District Data Analyst Interview Mark Brady 11/19/2012

  2. MS Excel Lookup Functions

  3. MS Excel Lookup Functions • These are functions that operate on lists or arrays of data • Data may reside in Workbooks, Databases, or text files • Most functions can be nested or combined with other functions • Best used for simple data table relationships Mark Brady 11/19/2012

  4. MS Excel Lookup Functions VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) lookup_value  - The value to search in the first column of the table array table_array  - Two or more columns of data( range or a range name). col_index_num   - The column number in a table array containing target values range_lookup  -  A logical value that specifies whether you want to find an exact match or an approximate match Mark Brady 11/19/2012

  5. An Example: Grassy Waters Preserve Rainfall Data MS Excel Lookup Functions Mark Brady 11/19/2012

  6. MS Excel Lookup Functions VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) 1 2 3 4 5 6 7 8 9 10 11 12 13 14 True – Exact Match False – Approximate Match Omitted – Approximate Match Mark Brady 11/19/2012

  7. MS Excel Lookup Functions VLOOKUP(BWPS_R,Rain_GrassyWaters,10,True) 1 2 3 4 5 6 7 8 9 10 11 12 13 14 True – Exact Match False – Approximate Match Omitted – Approximate Match Mark Brady 11/19/2012

  8. MS Excel Lookup Functions Mark Brady 11/19/2012

  9. MS Excel Lookup Functions Common Errors Encountered With VLOOKUP 1.  You Have Your Numbers Formatted as Text 2.  You Have a Trailing Space at the End of Your Values 3.  You Forgot to Reference Lock Your Arrays 4. Your logical condition (range_lookup) is not appropriate Recommended Uses for VLOOKUP 1.  As a simple query tool 2.  As a user interface to data stored in separate files or databases 3.  As a data validation tool (dynamic graph generation) 4. Complex queries are possible, but multiple nesting levels are often confusing to code and debug Mark Brady 11/19/2012

  10. Just For Fun! MS Excel Lookup Functions Typical I.F.F.I. Workflow: Pronounced….[If-ee] Initiative (can I solve this problem with this tool?) Feedback from Peers Identify errors (logic, coding, etc) Implement Solutions Mark Brady 11/19/2012

  11. Initiative Feedback MS Excel Lookup Functions YES!!! Initiate Solutions Identify Errors Mark Brady

  12. MS Excel Lookup Functions Questions? Mark Brady 11/19/2012

More Related