David H. Ringstrom, CPA Accounting Advisors, Inc. - PowerPoint PPT Presentation

Slide ID: 445 Written and Presented by David H. Ringstrom, CPA Accounting Advisors, Inc. www.accountingadvisors.com About the speaker: David is owner of Accounting Advisors, Inc., an Atlanta-based spreadsheet consulting firm that he started in 1991. Throughout his career David has spoken at conferences on Microsoft Excel, and written dozens of freelance articles about spreadsheets. He offers Excel and Access training and consulting services nationwide. Last Revised: 02/25/14

Slide ID: 2600 Introduction to VLOOKUP 2 of 23

Slide ID: 2607 VLOOKUP - #REF! Error 3 of 23

Slide ID: 2606 VLOOKUP - Text vs. Numbers 4 of 23

Slide ID: 2608 VLOOKUP with TEXT Function 5 of 23

Slide ID: 2601 Streamlining VLOOKUP 6 of 23

Slide ID: 2602 Using the Table feature with VLOOKUP 7 of 23

Slide ID: 2604 Introduction to the MATCH Function 8 of 23

Slide ID: 2605 VLOOKUP with MATCH 9 of 23

Slide ID: 1795 Viewing Two Worksheets at Once 10 of 23

Slide ID: 1807 Wild Card Lookups 11 of 23

Slide ID: 1808 Duplicate Data Trap  Formula returned 0 instead of -90 because 40100 appears on the list twice. Consider using SUMIF or COUNTIF . 12 12 12 of 23

Slide ID: 1810 Returning data from the Left  We can use the CHOOSE function to create an array of columns that VLOOKUP can use to return data from the left. First, let’s see a traditional use of CHOOSE . 13 13 of 23

Slide ID: 1811 CHOOSE inside VLOOKUP  In this context, CHOOSE creates an array of two columns. You’ll always specify two columns, and have VLOOKUP return data from the second column. Hat tip to Richard Schollar by way of Bill Jelen (aka Mr. Excel) for this clever use of the CHOOSE function. 14 14 of 23

Slide ID: 1815 MATCH/INDEX Example 15 of 23

Slide ID: 1818 Two-Way Lookup 16 of 23

Slide ID: 1820 SUMIF Function 17 of 23

Slide ID: 1822 SUMIFS Example SUMIFS typically has 5 arguments, but you may specify up to 128 criteria for a total of 129 arguments. 18 18 of 23

Slide ID: 1987 SUMPRODUCT SUMPRODUCT is a versatile function that can multiply cells together and provide a sum of the result. However, it can also serve as a multiple-criteria look-up function. 19 19 of 23

Slide ID: 2362 SUMPRODUCT 20 of 23

Slide ID: 2179 Formula Error Handling Evolution 21 of 23

Slide ID: 1845 VLOOKUP Week Web site  Bill Jelen (aka Mr. Excel) sponsored VLOOKUP week March 25-March 31, 2012. The web site contains dozens of innovative uses of lookup formulas that go far beyond what we’ve discussed today . http://vlookupweek.wordpress.com 22 22 of 23

Slide ID: 485 Questions? Spreadsheet Consulting help?  I’m happy to hear from you (e-mail is best) : David Ringstrom Accounting Advisors, Inc. 614 Park Avenue SE Atlanta, Georgia 30312 404-784-0275 david@acctadv.com  Get notified when I write new articles about Excel, et. al.: www.twitter.com/excelwriter www.facebook.com/accountingadvisors www. linkedin.com/in/davidringstrom