vlookup errors

Please tell your friends about the ability to earn continuing professional education hours on this site. Click here to email them

Training Index

CPD training for Professional

    SAICA/ IRBA CPD Training available on our new site http://www.AuditExcel.com or go directly to the SAICA CPD training page .

These pages are designed to allow people to learn about using Microsoft Excel in a structured way. We have also built in the functionality to allow you to test yourself and at the same time verify that you have completed the training. This will be especially useful for professionals who need to earn points or hours that can be verified as training for the year e.g. (CA) SA (Chartered Accountants), CIMA, Doctors etc. It can also be used by people who want to improve their excel skills and include proof of this training on their CV's.

If you are struggling to watch the video clips due to your connection speed or network, see the bottom of the page to find out how to get a CD's with all the video clips included.

NOTE:

Due to the limited participants, the SMS line has been discontinued by the network provider. While we search for an alternate solution you can mail us the answers and we will verify them from our side. Send you mail to info@AuditExcel.co.za
 

 

Section 10: VLOOKUP (CPD= 40 min)

excel spreadsheets VLOOKUP (7 min)

Learn to use Excel's VLOOKUP function to automatically look up a piece of information and then pull through corresponding information.

 

       excel spreadsheets Advanced VLOOKUP Concept (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).

 

VLOOKUP Exercise (20 minutes)

 

excel spreadsheetsVLOOKUP Exercise Solutions (7 minutes)

 

 

 

 

Question 10

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

SMS the letters CPD, the section number and answer to See Note Above (South Africa ONLY)

e.g. CPD 1F

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

excel spreadsheets Switching on your add ins (2 min)

In order to get the best out of Excel, 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.

 

excel spreadsheets EOMONTH (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.

EOMONTH Exercise (12 minutes)

 

excel spreadsheetsEOMONTH Exercise Solution (2 minutes)

 

 

 

 

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

SMS the letters CPD, the section number and answer to See Note Above (South Africa ONLY)

e.g. CPD 1F

Section 12: Text Functions (CPD= 20 min)

excel spreadsheets CONCATENATE + & (3 min)

Concatenate allows you to combine multiple cells into one cell.

excel spreadsheets LEFT/ RIGHT/ MID (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.

SMS the letters CPD, the section number and answer to See Note Above (South Africa ONLY)

e.g. CPD 1F

 

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

excel spreadsheets IF (2 min)

The IF function allows you to perform alternate actions depending on certain criteria.

 

excel spreadsheets AND/ OR functions (4 min)

The AND/ OR functions allow you to check multiple criteria that they either all match or some of them do.

Data Cleanup Exercise 4 (11 minutes)

Follow the instructions in the file. The exercise includes the VLOOKUP, EOMONTH, CONCATENATE, IF/ AND and OR functions.

 

excel spreadsheetsData Cleanup Exercise 4 Solution (5 minutes)

 

 

 

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

SMS the letters CPD, the section number and answer to See Note Above (South Africa ONLY)

e.g. CPD 1F

 

To understand the terms and conditions of receiving the certificate of completed modules please visit this page- CPD Terms and Conditions

To find out more about getting all this training material on a convenient CD visit this page - All video clips on a convenient CD.

Don't like what you see. Tell us at training@AuditExcel.co.za .

Do you have suggestions of what you would like to see. Tell us at training@AuditExcel.co.za .

 

 

 

Home | Contact Us | Sitemap | Training | Sarbanes Oxley | Articles | Links | Blog