turning numbers into knowledge nate moore mba, cpa, cmpe
TRANSCRIPT
Turning Numbers
Into Knowledge
Nate MooreMBA, CPA, CMPE
Verifiably Vital Virtues Vested in Versatile
Microsoft Excel® VLOOKUP
Learning Objectives
• Work through a basic VLOOKUP
application
• Combine functions to add power to
VLOOKUP
• Watch for and overcome VLOOKUP
gotchas
Work Through a BasicVLOOKUP Application
• VLOOKUP(lookup_value, table_array,
col_index_num, [range_lookup])
Lookup Value
What am I looking for?
Table Array
What table has the data I’m looking for
(in the far left column)
Column Index Number
Which column in the table
do I want to return?
Range Lookup
TRUE – Find the closest value to what
I’m searching for, without going over
FALSE – Find an exact match
Range Lookup – Continued
Range Lookup is Optional and
TRUE is the default!
Table has to be sorted for TRUE to
work!
Combine Functions to Add Power to VLOOKUP
• IFERROR (trap and control errors)
• LEFT (lookup leftmost x characters)
• ROW & COLUMN for column number
• HLOOKUP
Watch For and Overcome VLOOKUP Gotchas
• Text vs. Numbers (especially CPT
codes)
• Convert Text to Numbers and vice
versa using Text to Columns on the
Data tab
Watch For and Overcome VLOOKUP Gotchas
• Absolute references for the table
($A$2:$C$198)
• TRUE is the default!
Applications for VLOOKUP in a Medical Practice
• Fee Schedule Analysis – CPT Codes
• Diagnosis Codes
• Patient Satisfaction Surveys
• Financial Reporting
Next Steps
Using VLOOKUP to Combine Data in
Microsoft Excel article at
mooresolutionsinc.com/articles.php
MGMA Excel Users Community
More Next Steps
Watch Excel Videos 61-69 and 173-176
at mooresolutionsinc.com
Practice with this sample data
How to Join Excel Users
• Login to www.mgma.com• Go to My Profile, then click on My
Subscriptions from the submenu• Choose your delivery preferences for the
communities you wish to join• Direct link
http://community.mgma.com/MGMA/MGMA/MyProfile/MySubscriptions/Default.aspx
• Excel Users is in alphabetical order