MS Excel Lookup Functions

# MS Excel Lookup Functions

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