Lookup functions

Dear Friends

Hope that casting errors have now become a history. In this article we would dwell upon one of the most powerful function of excel namely LOOKUP.

This time around Bahadur Singh was given a due diligence assignment on the financials of a company for a particular year. To begin with, he met the client and asked for the closing and opening trial balance. The company had approximately 9000 account codes and hence the print out given by the client was 150 pages each for closing and opening trial balance. Seeing this our friend was shocked and he had no idea as to how would he check if the opening balance for each and every account code matches with the closing balance. He thought for a day and than came to us to seek our opinion as to if there is some better way to do the same.

Traditionally there are two processes, which we follow for such an activity:
1. Send a couple of staff to the client place and then do a calling comparing i.e. one person would call the account code and amount from a particular trial balance and the other person would check the same in other trial balance.
2. In case the trail balance is as huge as described in our example above than we carry out a test check basis based on some samples.

I hope that many of you would agree that these are the two methods followed by us when it comes to checking of balances before starting our audit or due diligence assignments. Let us see what are the faults or risks in carrying the above two methods:
1. The first method as described and in practice is very clumsy. We can follow the same in case the trial balance is small, but it is not practical to follow while we are engaged in an assignment with big companies. Also the calling and comparing method is not a foolproof method. There is always a probability of human error due to the mundane process involved. Also what we do after this process is over? We file these print outs in our audit file. At this point of time let us ask a question to ourselves, is this appropriate documentation? I doubt. The process itself is full of errors and we document that process to ensure that we had documented it as per the required standards. Hence it would be apt to say that the first method should not be followed in case of big companies and it may be followed in case of small sized companies but still a better solution ought to be there.
2. The second method that many of us follow is even more dangerous. Sampling should be done in case a particular activity is impossible to be carried out for the entire population. Like in case of vouching for expenses we rely on sampling. It may be appropriate, as vouching each and every expense item is impossible. But application of sampling in case of matching opening and closing balances is not recommended.

In such a case what are we left with. Do we have any better solution? This remembers me of a maxim ‘ignoratia law non excusat’ that means ignorance of law cannot be an excuse to justify a sub standard quality of work. Similarly I see a maxim in future, stating ‘ignoratia tech non excusat’ that would mean ignorance of technology couldn’t be an excuse to justify a sub standard quality of work. If we are using a particular technology (in our case excel) and just because we are not aware of its functions it would not be an excuse to justify a sub standard quality of work. In these kind of cases we should make use of LOOKUP function available in excel. To make it more generic whenever we are faced with a situation where we want to retrieve values from a reference file and match these with the data contained in the transaction file we should use LOOKUP function.

Below we detail the function in detail, explaining Bahadur Singh how he can apply the same in his case and get the balances match in less than one minute.
1. Ask the client to give a soft copy of both opening and closing trial balance. Given below is an extract of both the trial balance:

File 1




File 2


Col A
Col B
Col C
Col D

Col A
Col B
Col C
Col D
Trial Balance 2004-05



Trial Balance 2005-06




Open
Close



Open
Close
Account
Code
Account
Description
Amt
Amt

Account
Code
Account
Description
Amt
Amt
21121
Mr. ABC
2000
0

21121
Mr. ABC
0
0
21125
CVOCA Assoc
5000
1000

21125
CVOCA Assoc
1000
200
21238
Hasmukh Dedhia
1500
2000

21238
Hasmukh Dedhia
2000
0
21251
Chirag Savla
1000
50

21251
Chirag Savla
55
5000

2. File 1 is the trial balance for 2004-05 i.e. for previous year and File 2 is for 2005-06 i.e. for current year. Hence File 1 becomes our closing trial balance and File 2 is our opening trial balance. Now we would like to match column D of File 1 with column C of File 2.
3. To achieve our objective we would use the VLOOKUP function. Go to File 2 and in column E against the first account code i.e. 21121 (assume it is in row 6) write the following formula =VLOOKUP(A6, ‘File 1’$A$6:$D$9,4,false). Copy this formula in all the rows below. The output would be as follows:

Col A
Col B
Col C
Col D
Col E
Trial Balance 2005-06





Open
Close

Account
Code
Account
Description
Amt
Amt

21121
Mr. ABC
0
0
0
21125
CVOCA Assoc
1000
200
1000
21238
Hasmukh Dedhia
2000
0
2000
21251
Chirag Savla
55
5000
50

4. Let us first understand the formula that we have written in step 3 above. We said
· VLOOKUP – meaning vertical lookup
· A6 – The value available in cell A6. This is the account code for which we want to get a particular value.
· ‘File 1’$A$6:$D$9 – This is the range of cells and the file from which that value needs to be derived. In our case the source value was in file 1 in the range given.
· 4 – This refers to the column number of the source file from which the value needs to be derived. In our case we needed to pick value from column D that was the fourth column from account code.
· False – Here we command the system to find an exact match.
5. Now in column F of file 1 we give the command column C – column E and get to a conclusion that wherever the amount is not equal to zero the balances are not matching.

In case one finds it difficult to type the VLOOKUP command one can always use the function wizard from Insert >> Function.

What are the benefits to use this function:
· An activity that would otherwise need a few man-days can be completed in less than a minute.
· It is a foolproof method to replace any kind of calling and comparing of figures in excel that we carry in our day to day activity.
· On a lighter note it saves a huge lot of paper and also the soft copies would be a perfect documentation.

Further you would have noticed that I have used the word LOOKUP at times and VLOOKUP at times. Here LOOKUP can be VLOOKUP or HLOOKUP. VLOOKUP stands for vertical lookup and HLOOKUP for horizontal lookup. In our case above since that data arrangement was in a vertical format we used VLOOKUP. In cases where the data arrangement is in horizontal format we should use HLOOKUP. I have not specifically covered the explanation to HLOOKUP as it is in line with VLOOKUP.

Well, no more calling comparing henceforth. Bahadur Singh was the happiest person as we would have saved at least a couple of weeks for him. We shall continue with more excel features in the forthcoming issues.