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

▶

Feb 22, 2023 142 likes •384 views

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

SLIDE 1

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

SLIDE 2

Introduction to VLOOKUP

Slide ID: 2600 2 of 23

SLIDE 3

VLOOKUP - #REF! Error

Slide ID: 2607 3 of 23

SLIDE 4

VLOOKUP - Text vs. Numbers

Slide ID: 2606 4 of 23

SLIDE 5

VLOOKUP with TEXT Function

Slide ID: 2608 5 of 23

SLIDE 6

Streamlining VLOOKUP

Slide ID: 2601 6 of 23

SLIDE 7

Using the Table feature with VLOOKUP

Slide ID: 2602 7 of 23

SLIDE 8

Introduction to the MATCH Function

Slide ID: 2604 8 of 23

SLIDE 9

VLOOKUP with MATCH

Slide ID: 2605 9 of 23

SLIDE 10

Viewing Two Worksheets at Once

Slide ID: 1795 10 of 23

SLIDE 11

Wild Card Lookups

Slide ID: 1807 11 of 23

SLIDE 12

Duplicate Data Trap

12 12

 Formula returned 0 instead of -90 because 40100 appears on

the list twice. Consider using SUMIF or COUNTIF.

Slide ID: 1808 12 of 23

SLIDE 13

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.

Slide ID: 1810 13 of 23

SLIDE 14

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.

Slide ID: 1811 14 of 23

SLIDE 15

MATCH/INDEX Example

Slide ID: 1815 15 of 23

SLIDE 16

Two-Way Lookup

Slide ID: 1818 16 of 23

SLIDE 17

SUMIF Function

Slide ID: 1820 17 of 23

SLIDE 18

SUMIFS Example

SUMIFS typically has 5 arguments, but you may specify up to 128 criteria for a total of 129 arguments.

Slide ID: 1822 18 of 23

SLIDE 19

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.

Slide ID: 1987 19 of 23

SLIDE 20

SUMPRODUCT

Slide ID: 2362 20 of 23

SLIDE 21

Formula Error Handling Evolution

Slide ID: 2179 21 of 23

SLIDE 22

VLOOKUP Week Web site

 Bill Jelen (aka Mr. Excel) sponsored VLOOKUP week

March 25-March 31, 2012. The web site contains dozens

f innovative uses of lookup formulas that go far

beyond what we’ve discussed today.

http://vlookupweek.wordpress.com

Slide ID: 1845 22 of 23

SLIDE 23

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

Slide ID: 485

www.twitter.com/excelwriter www.facebook.com/accountingadvisors

www. linkedin.com/in/davidringstrom