Excel Tables

Excel Tables

Excel Tables

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

1. Excel Tables 8 April 2014

2. Excel: More Ways to Select • CHOOSE: a value or action from a list • Converting numbers to words • survey scale values to words • Best used if already have integers

3. Finding an Item in a table • Choose one from a list • CHOOSE had to be a list • INDEX allows use of a table • Here’s a good time to use Names!

4. Table Rather Than a List • INDEX: choose an item by position • Example: • INDEX(list, selector) • Returns the value at the selector position

5. Find a Value in a Table:Get the INDEX! • MATCH: a value from a list • Example: • MATCH(looking-for, list-of-possible[, match type]) • Returns the position • Match-type • 1 largest value less than or equal • 0 exact • -1smallest value greater than or equal

6. Going the other way • Finding the location of a value • In tables: MATCH returns the location INDEX extracts • MATCH has two versions: exact or range

7. Combining the Functions • VLOOKUP and HLOOKUP • VLOOKUP = MATCH and INDEX • Column 1 has the value you are searching • Can tell it which column to return • ASCENDING Range or Exact • Use when matches your needs

8. Special Cases • If it’s DESCENDING data, Use MATCH and INDEX • If you only need to check existence Use MATCH Check ISNUMBER, ISERROR, IFERROR Replace with appropriate text

9. Dealing with Errors • Let them propagate until the end • And THEN change using ISERROR or IFERROR

10. Merging Tables • Physically or logically • Sometimes helpful to have a merged table… • …but it’s not necessary • No new functions needed