REAL VLOOKUP ASSIGNMENTS IN EXCEL

INSTRUCTOR
Andrey Ampilogov, based in Rusia, currently works as SPSS programmer. He holds a Bachelor of Applied Science (BASc), Quantitative Economics. Andrey has held several positions as  senior analyst at major financial institutions in Rusia and the US.

DESCRIPTION
This challenge consists of 20 exercises focused on the Lookup, Reference and Database functions of Excel, especially the VLOOKUP. All exercises are real, based on business cases where this type of functions are a must. You will practice  all types of lookup functions  such as vlookup, hlookup, reference and database functions like index, match, sumif, sumproduct, indirect and many others in different scenarios that tend to occur at corporate level.

Real Practice

All assignments come with a real set of data and describe a regular situation or problem that you will have to solve using the most suitable Excel function. Basically, these exercises replicate real world problems or situations that you may encounter at any position of wide range of  industries.
Moreover, the workload is mixed, some exercises are short, finished with just one formula, but others are rather mini projects where you will have to implement and combine several formulas to yield the expected outcome.
Once you finish all the exercises, you will be more confident with Excel and gain the expertise and knowledge required to succeed any job interview.

WHAT YOU WILL PRACTICE:
1) Find, match and retrieving values of multiple tables and/or sheets in order to get insights about a business. Not only vlookup but also other formulas used to retrieve values from one place to another.
2) Confront the situations when lookup functions do not work and learn how to fix them.
3) Common Excel tools and tricks to handle and analyze data.
4) Tools and tricks to work with massive data sets (more than 60 000 rows).
5) Real applications of the following functions: vlookup, sumif, hlookup, index-match, ifna, indirect, sumproduct, offset, find…
6) Nested functions.
7) Text functions: len, wide, concat, substitute, left, lower, etc.
8) Conditional statements.

REQUIREMENTS
90% of problems require an intermediate level of Excel. Students must have attended a complete course of Excel and feel confident with the tool. Five exercises are considered of advanced level.
The spreadsheets included in the project have been done with the latest Excel version. It is then compatible with Excel 2007, Excel 2010, Excel 2013 and Excel 2016.

DOWNLOAD
You will receive a protected zip folder and its password  in the email confirmation after purchase.
The file is made up of 2 PDFs and 42 Excel files:
– PDF with assignments.
– PDF with the  solutions (57 pages). It includes detailed explanations and print screens with the  formulas required to solve each problem.
– 22 Excel files with the data required for each exercise.
– 20 Excel files with the solutions to each exercise.

CONTACT
If you need additional information, do not hesitate to contact us.