The reasons for the vlookup #Ref error and where you should look to correct it both where the #REF error only appears in the value, or where it appears in the value and formula
Exercise in understanding reasons that the VLOOKUP is giving the incorrect answer
In this example you are calculating the tax liability based on various salaries. The tax table is provided and a series of salaries are provided. You need to calculate what the tax liability will be on these amounts.
In this example we have a situation where different levels of sales result in different commission rates. Calculate the commission % allowed and the commission earned
Another example in using VLOOKUP approximate match to categorize items in Excel.
Example in using VLOOKUP to categorize data in excel.
How to use vlookup approximate match to categories data, use banding and get Excel to guesstimate results
VLOOKUP exercises download for use in the rest of the VLOOKUP course with practical examples and detailed explanations of the solutions.
A common requirement in Excel is to join databases with VLOOKUP. This often happens when there is a common item in the databases. Examples of this are when you want to show product codes and names or show a client number and client name if they are on different lists.
We can also use VLOOKUP to find items that are missing from a list. This is very useful when reconciling accounts.
A useful feature of VLOOKUP is its ability to anchor cells. A common problem is that data appears in different cells at different times (each time you download a report a key cost item you are looking for appears on a different line). You can use VLOOKUP to find the cost item and make it appear on a set row. You can then build ratios and graphs off this static row
An exercise with VLOOKUP to understand why it is not giving the correct answer and one of the causes of a #n/a error when using VLOOKUP.
An exercise on how to create automatic sorting in excel so that the information automatically sorts itself when changes are made (no VBA or Macros)
Data tables are an exceptionally useful function in Excel which allows you to (quickly) run sensitivities on you financial models. The limitation however is that at best you can only run a 2 variable data table. This can be limiting as often a financial model has 3 or 4 key inputs which you want to test. Below is a method to create multi variable data tables in excel.
In this lesson you will be able to download the various templates which allow for 3 and more way data tables. Watch the video clip to see how to use them in you own financial models for enhanced sensitivity analysis
How to use the Excel functions wizard to find, understand and use all the various functions and formula available in Excel
An exercise in using the Excel Function Wizard to find a function that allows you to find the 2nd biggest number in a list.
How to use the Formula Auditing Tool to check which cells are relied on in a formula, identify differences in rows and columns, and check cell importance before we delete it
How to use the Excel $ sign to create formula that can be copied to other cells and they will maintain their integrity. This will allow you to build a single formula to accommodate multiple situations instead of building each formula separately
Tips and tricks for using $ in Excel formulas including how to safely and easily create them
Exercises on using the Excel $ sign in formula
The IF function is one of first formula you should learn to use. In basic English it says: "IF something happens do this, otherwise do that". In Excel it can be used in a number of places.
Excel IF Exercises showing you the various options and possibilities with the IF function
The National Consumer Tribunal Consent Order Assistant is to assist Debt Councillors in providing the NCT with the required information to assess the case. The tool is built in Microsoft Excel and consists of the following sheets with a brief description of each sheet.
Initial setup instructions for the COA from NCT
Finalising the letters based on the information captured into the COA
Checking the financial details captured onto the COA before sending it to the NCT with calculation checks and a debt repayment graph
Step 2 in the process to capture a case onto the COA
Step 1 of the process to capture a case on the COA
Overarching rules of the COA which are applied throughout the spreadsheet
Exercise to be completed for calculated columns
Build your first VLOOKUP and understand what each component means and how to use it.
Vlookup explained in simple terms so that you obtain a fundamental knowledge that you can apply to the more advanced uses of VLOOKUP