“Have You Ever Wanted To Pull Specific Information From Excel Tables Dynamically, But The Complicated Lookup Syntax, #N/A Errors, And The Data Itself Have Always Prevented You From Getting Started?”
“Here’s how to easily and professionally implement the key lookup formulas used by experts to compare and analyze data, build dynamic reports, move huge amounts of data and more (Today Starting From Scratch)”
From: John Franco
Dear Excel user,
My name is John Franco, Excel author, and you know VLOOKUP and INDEX & MATCH are the proven tools professionals use to accomplish many many things with their data, from pulling the information they want from tables and create dynamic reports, to moving huge amounts of data between Workbooks/Worksheets without copying and pasting (without making any mistake, neither writing any macro)…
…but you are reading this because you know these lookup formulas are not so easy to implement as some gurus suggest in forums and blogs.
If you have ever had any of the following problems, then you’ve got to read every word on this letter…
- You get N/A# errors instead of meaningful results because you don’t have enough practical experience applying the VLOOKUP/INDEX & MATCH theory into real scenarios
- You are bored of referring to help or prior examples due to low frequency of use of these functions. Your lack of regular use makes you uncomfortable using these formulas. You don’t remember the lookup syntax. You are not truly sure about what each lookup term really means. You don’t like typing the syntax
- You can’t write them correctly because you don’t fully understand the definitions of these functions,. E.g. you don’t know what the TRUE/FALSE criteria really mean in VLOOKUP/INDEX & MATCH
- You can’t find the problem when your formulas don’t find exact matches because you don’t know the principles that need to be observed to get the right setup of your data.
I failed too many times implementing these lookup formulas because I made the mistakes made by most Excel database newbies out there…
Mistake #1 – Dealing with low quality Data
Your data is coming from several uncontrolled sources like: other Excel books, text files, PDFs, webpages, etc. And many of those sources might also obtain data from other sources as well. So data could be a mess.
Writing a perfect lookup won’t work if you refer to a messy table, containing misspellings, extra spaces, undesired characters, etc.
The mantra is Data quality, Data quality, Data quality.
Some data quality guidelines you can follow are:
- Remove undesired characters from your tables, e.g. spaces, weird characters, etc
- Check data types. Numbers should not be stored as texts, dates should be entered properly, etc
Mistake #2 – Hoping VLOOKUP will work
I used to start typing =VLOOKUP(… in the formula bar before understanding what each term truly means, without auditing data, and hoping it will work. When I hit Enter, I was always nervous about the result I was about to get.
I blamed #N/A errors.
And to be sincere with you, I didn’t know where to look at to fix the non-matching problem.
Of course I was very happy when it worked! I felt lucky, but my hoping approach was just unprofessional :(
Some guidelines to turn lookups into an exact science are:
- Understand how to enter arguments: arrays, dates, etc.
- Be absolutely sure you understand what each lookup argument means
- Use only high quality data (you can clean data before use)
Mistake #3 – Lack of trust in lookup formulas
Some people believe Access should be used over Excel, to deal with databases and relational tables. And so some users truly believe Excel lookup formulas are just unreliable and not worth a try.
For some time I lost my confidence in using these formulas until I realized that I didn’t relate tons of tables in Excel but just a couple of flat tables at maximum. I also realized that databases are rigid, and we need the flexibility that Excel offers, for moving cells around, formatting a sheet with your company logo, applying your own colors, adding a side note to a record, adding a chart, etc.
Lookup formulas work perfectly in an Excel context not in an enterprise database context, the same way a car works in a city context, not in a Formula 1 context. It’s just a specific tool for specific conditions.
After making these mistakes, I mastered VLOOKUP and INDEX & MATCH FORMULAS. This is the true story about…
…How I used VLOOKUP and INDEX & MATCH to complete my data assignments
Some years ago when I was working as a Civil Engineer, for one of the 50th construction contractors in the world, floods of data came to me. I was beginning and one of my first assignments was just consolidating exported tables from a centralized and awful corporate software, and sending the master Excel table to my boss, so he could use nice Excel formulas and tools to get the results he wanted.
I hated that meaningless job and I was vulnerable…
…I could not say “NO” to those boring assignments (you know, I was the lowest person on the ladder). So I needed to get the job done.
Things were not so easy because I was starting with Excel and knew very few about what formulas or macros I needed for merging tables more effectively. So I did it manually, you know, scrolling big sheets to find data and then copying and pasting, all day long.
At first, copying and pasting worked well, I delivered clean data and everything was ok. My boss could perform any analysis with the data: filtering, sorting, etc.
But things got worse and worse when the data was updated by the corporate software and the dirty and messy data came to me again, sometimes after a couple of hours after I had delivered the clean data to my data-hungry boss.
Then, I said to myself: there should be a better way to do this!
I committed to find the better way!
And I did it.
I discovered that one does not need to learn macros to become a data management expert.
I discovered what VLOOKUP and INDEX & MATCH formulas could do for me. And I learned that they can do a lot. And they will do a lot for you too!
- Pulling specific data dynamically to create any report layout (horizontally, vertically, etc)
- Rearranging a table layout dynamically. Turning a table upside down in a matter of minutes
- Managing tables efficiently (without copying and pasting data and minimizing human error): generating a new table based on existing tables, moving data between Workbooks/Worksheets, performing tables reconciliation, merging two or more tables into one
- And a lot more…
I have been using VLOOKUP and INDEX MATCH since then with amazing results!
I am about to reveal the fundamentals to write perfect VLOOKUP and INDEX & MATCH formulas…
If you follow my advice and implement these lookup techniques and principles, you will be soon very popular among managers and decision-makers in your company, because performing lookups is the single most essential skill when it comes to handle data…
“I must say that this is very valuable content. I have used many Excel tutorial books when I was starting off but yours is leagues above.
I never thought that somebody could give the Lookup function so much thought and studied it so much in depth. We could definitely benefit from your mega work.
What I like best in your book is the language; I really like the free flow expression of ideas through practical and easy to relate to illustrations…”
Abhishek Raju | Lead Researcher at Indian Institute of Management | Bangalore Area, India
“I wanted to say…Thank-you for such a beneficial, enjoyable and teaching style for conveying this lookup material. After completing this program I finally completed a 10 year quest in understanding Excel data management. I was so excited in connecting the dots that I could not pull myself away from the data manipulation of some old databases for the last 72 hours.
Thank-you for a quality product.”
Phil Egan | Corpus Christi, Texas
“I Love you John Franco, you make me a master at Excel.”
Nipun | Subscriber
“Dear John…Really you are fascinating and have an explanation method that is highly attractive. Your approach impressed me because it hits the point directly…”
Emad T | Student
“You bring to light useful functions/methods/approaches otherwise unknown to a significant portion of Excel users.”
Anonymous | Excel subscriber during a survey
“As A teacher I love your simple creative ways of explaining complex subjects, it was through your teaching I learned so much more and in turn shared with my students. To this day I use your rules about designing a good table. I always enjoyed your teaching style and I am thrilled to know you have returned to Excel.”
Debra | Student
“Dear John, …I want you to know how blessed I am meeting someone like you with this craft of yours…!It’s really helpful and easy to understand. Nothing more to say, Ill keep this for life and share to my colleagues… Wish you the best of everything through the years to come. Muchas Gracias.”
Garry Pasan | Student
“Fantastic explanation with easy to learn description of complex Excel formulas with clear examples and screen shots/files.”
Anonymous | Excel subscriber during a survey
Announcing the Professional Excel lookup training…
How To Pull Specific Information From Excel Tables Using VLOOKUP and INDEX & MATCH formulas
Step by step guidance to set up and troubleshoot lookup formulas.
Included in this in-depth and easy-to-follow guide are secrets most Excel users never discover… such as:
- How to understand and remember VLOOKUP and INDEX and MATCH syntax and how to write them like a pro. Develop an intuitive way to look at the lookup arguments that just sticks, even if you don’t use these formulas often. Develop the confidence and right frame of mind to use VLOOKUP and INDEX and MATCH in a broad range of data scenarios, from pulling data to moving data between sheets
- How to prepare the data before using VLOOKUP and INDEX and MATCH so you trust the results you get and can send crucial reports with total confidence to upper management or customers. If you want to stand out from the data newbies crowd, this data quality skill will make you a trusted data professional
- How to systematically setup the VLOOKUP and INDEX and MATCH formulas so you trust the results you get, even if you get the infamous #N/As (sometimes they are right). No more trial and error to find the sources of errors like #N/A and #VALUE. No more frustration when you get unexpected error results because now you know exactly where to look at to locate the error. Detailed lookup implementation checklist provided
- You will learn the SPECIFIC situations where you can use these formulas in. Learn how to lookup texts, dates and numbers, what types of tables are not suitable for VLOOKUP, what kind of tables are not suitable to lookup formulas (avoid dealing with duplicate results). Increase your confidence to deal with challenging data scenarios
- How to troubleshoot lookup problems. This is specially helpful when you have data and reports from other sources. Learn how to find and fix non-matching errors (#N/A) and other typical errors as well (#REF!, #VALUE). Learn how to diagnose and solve typical problems like extra spaces, numbers stored as texts, weird and undetectable characters, etc. You will never ever be in the dark and doubt anymore when you don’t get a matching. Checklist provided so you always deliver results, even if you face challenging data projects
- Step by step instructions to make multiple column/criteria VLOOKUP and INDEX and MATCH formulas. This is specially helpful when you want to get a match by more than one column, for example, if first name and last name are in separate columns
- See closely how lookup formulas work in real scenarios using the practice files. Secretly put your hands on done-for-you lookup files where you can explore how these concepts are put into practice
…and much more!
Greatest Gold-Mine of Proven “Excel lookup training” Ever Crammed Into One Excel Program…
Win Acclaim implementing these core techniques (200+ pages with step by step guidance, detailed illustrations and practice files)…
Section I – Lookup Syntax Mastery
- Why you use tables
- Why you use lookup in Excel tables?
- VLOOKUP syntax demystified
- INDEX & MATCH syntax demystified
- Multiple criteria syntax
- Syntax of big formulas
- How to gain experience with VLOOKUP and INDEX & MATCH formulas
- Main differences between VLOOKUP and INDEX & MATCH
- VLOOKUP and INDEX & MATCH limitations
- VLOOKUP and INDEX & MATCH advantages
- The 10 most typical lookup mistakes
Section II – Table Set Up
- What’s an Excel table
- How can lookup formulas use information from different tables
- The 10 don’ts to consider when using Excel tables
- The 10 dos to consider when using Excel tables
- Typical dirty data scenarios
- How to detect and fix dirty data
- How to cross check if your data is ready for searches
- How do you get the same results using several mediums
Section III – Troubleshooting
- Lookup troubleshooting introduction
- The psychology of non-matching problems (#N/As)
- How to get a perfect match?
- Preventing/troubleshooting unconvincing #N/A messages
- Common lookup errors to look for
- Interpreting genuine #N/A messages
- How can you replace a #N/A value by a meaningful message?
- How to diagnose and fix other lookup messages
And much more…
Important: this program includes practice files compatible with Excel 2003 and later versions.
My advanced Excel lookup guide “How To Write Dynamic VLOOKUP And INDEX & MATCH Formulas” for ABSOLUTELY FREE! ($47 value)
This easy-to-follow Excel guide will show you:
- What’s a dynamic formula and how to build one
- How to get the lookup formulas to work correctly when the source table is dynamic in terms of number of rows and columns
- …and much more
This guide also comes with a sample Excel file so you can get some real hands on experience!
Here’s how to get started!
You might be able to figure it all out on your own. Eventually.
Or you can take advantage of the 10+ years John’s already invested in hard work, researching, testing and implementing Excel lookup techniques.
There is absolutely no sane reason why you should want to waste years of your own life attempting to cover this same trial-and-error ground.
Excel lookup knowledge is scattered through books, forums, and blogs and synthesizing that information requires lots of work and motivation. You might spend months and end up with partial knowledge because gurus don’t have time to mentor you along the way and usually they don’t expose everything they know.
If you can get a better job or a promotion with these in-demand data skills, the price of this program is ridiculous.
The “How To Pull Specific Information From Excel Tables Using VLOOKUP and INDEX & MATCH formulas” program is USD 99
This includes private access to the Download Area where you can download your guide, practice files, bonuses and updates for 6 months, and email support for 1 month.
If you want to get exactly the information you need in just a few minutes of time, then you can explore one principle at a time, at your own pace.
If you’re serious about getting started on VLOOKUP and INDEX & MATCH for handling data, analysing data and presenting data for upper management and users, go ahead and click the “Get started today” button below.
Delivered instantly in PDF format, via a download page.
If you have any question about this product, contact us.
100% No risk guarantee
You love it or your money back
If within 60 days of purchase, you follow the program lessons, do the homework and cannot fully understands the lookup syntax, increase your confidence to deal with any kind of data, neither create your own dynamic reports, or if you simply didn’t like the material, just ask a refund and you will get your money back, no questions asked.
Just contact us (use the subject “refund”). Will take care of it.
- Private member area. Download guides, support material and updates in a private, password-protected area. Enter the area using your own credentials: user and password
- Direct interaction with the teacher. Ask your questions directly to me, by email or via our contact form, and I will answer your questions personally
Enroll now and start learning the key techniques to build interactive and dynamic Excel dashboards
Disclaimer: ClickBank is the retailer of products on this site. CLICKBANK® is a registered trademark of Click Sales, Inc., a Delaware corporation located at 917 S. Lusk Street, Suite 200, Boise Idaho, 83706, USA and used by permission. ClickBank’s role as retailer does not constitute an endorsement, approval or review of these products or any claim, statement or opinion used in promotion of these products.