Download
ms excel lookup functions n.
Skip this Video
Loading SlideShow in 5 Seconds..
MS Excel Lookup Functions PowerPoint Presentation
Download Presentation
MS Excel Lookup Functions

MS Excel Lookup Functions

205 Views Download Presentation
Download Presentation

MS Excel Lookup Functions

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