CPD Training for professionals

Section 10: VLOOKUP (CPD= 40 min)


NEW: Go to  VLOOKUP Explanations with downloadable exercises and detailed solution


VLOOKUP video tutorial (7 min)
Learn to use Excel’s VLOOKUP function to automatically look up a piece of information and then pull through corresponding information.

Advanced VLOOKUP Concept video tutorial (6 min)
Learn a technique that will enable you to use the VLOOKUP function even when you expect that there will be significant changes to your model.

If you were unfamiliar with the VLOOKUP function previously and have now learnt about it, don’t forget to look at the errors that can occur with VLOOKUP to cement your understanding (see above in How Errors Happen).

Click here to download the Vlookup_Exercises

VLOOKUP Question

By omitting the final condition in a VLOOKUP command (TRUE/ FALSE indicator) you are telling Excel to:
Answers:
A- Find the exact match
B- Find an estimated match based on the order of the underlying data
C- The function will not work without the final TRUE or FALSE

Section 11: End of Month functions (CPD= 20 minutes)

Switching on your add ins video tutorial (2 min)
In order to get the best out of Excel (excel 2003 only), you should load the tools that come standard with the product. In this clip we load the Analysis tool pack to give us additional functions.

EOMONTH video tutorial (4 min)
The EOMONTH (end of month) function allows you to generate a month end date no matter what the original date. Useful if you want to report with standardised dates.

Click here to download theEOMONTH_exercises

Question 11:

After using the EOMONTH function on a cell with 1 Feb 2004 (leap year), the answer will produce
Answers:
A- 1 Feb 2004
B- 28 Feb 2004
C- 29 Feb 2004

Section 12: Text Functions (CPD= 20 min)

CONCATENATE + & video tutorial (3 min)
Concatenate allows you to combine multiple cells into one cell.

LEFT/ RIGHT/ MID video tutorial (3 min)
Break up text into smaller components by taking characters from the LEFT, RIGHT or MIDdle.

Question 12:
Which of the following statements is FALSE?
Answers:
A- You can use CONCATENATE or & to attach form combined text
B- In order to create a space you need to use quote marks i.e. ” ”
C- The LEFT command starts from the right side and works to the left.

Section 13: Logical functions (IF, AND, OR) (CPD= 20 min)

IF video tutorial (2 min)
The IF function allows you to perform alternate actions depending on certain criteria.

AND/ OR functions video tutorial (4 min)
The AND/ OR functions allow you to check multiple criteria that they either all match or some of them do.

Click here to do an exercise (Data Cleanup 4). The exercise includes the VLOOKUP, EOMONTH, CONCATENATE, IF/ AND and OR function

Question 13:
Which of the following statements is FALSE?
Answers:
A- The AND function assesses whether the results of all the arguments are TRUE
B- The OR functions assesses whether the results of all the arguments are TRUE
C- The OR function assesses whether the results of some of the arguments are TRUE