Section 10: VLOOKUP (CPD= 40 min)
Want to learn more about Microsoft Excel? If you prefer attending a course and live in South Africa look at the Johannesburg MS Excel 3 Day Advanced Course or the Cape Town MS Excel 3 Day Advanced training course. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.
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