Hi ,
The whole point behind using formulae in a workbook is that once they are set up correctly ( which means not that they give the correct results , which even erroneous formulae can , but that their logic is correct ) , they will continue to give the correct results all the time.
The only way in which things can go wrong is if the formulae have been written without considering all possible situations such as insertion of rows / columns , deletion of rows / columns , modification of row / column headers ,...
However , there is a point beyond which developing an absolutely fool-proof formula is impossible ; it is up to the user to exercise some amount of discipline.
To doubt a VLOOKUP formula is not correct ; instead see if you can understand all the situations in which it can fail and then see how best these situations can be taken care of.
A VLOOKUP formula takes a value which is to be looked up in a data range , and if it finds a match , returns the corresponding value from a different column in the same data range ; the matching can be done either exactly or approximately.
In your sample workbook , the value which is to be looked up is the text Gross Profits in cell A4 on the Totals tab. The data range in which this is being looked up is A3:C30 on the Income Stmnt tab. The value which is being returned if the VLOOKUP function finds a match is from the second and third columns , the second column containing data for 20X2 and the third column containing data for 20X1.
The only possible mixups that can occur are :
The text in cell A4 on the Totals tab does not match the text in column A on the Income Stmnt tab.
The columns on the Income Stmnt tab which refer to data for 20X2 and 20X1 are changed by inserting columns in between column A and columns B , C ; in this case , the formulae will return the wrong values , since the second and third columns which are being referenced may contain some other data , and not the data for 20X2 and 20X1.
The first situation cannot be rectified by any known formula , since if there is no match , data can never be retrieved.
The second situation can to some extent be taken care of by matching the column header to see if it contains 20X2 and 20X1 as the right-most 4 characters ; if you want , the formula can even consider 20X2 and 20X1 anywhere within the column headers.
If however someone were to delete the columns B or C altogether and then recreate the data elsewhere on the Income Stmnt tab , no formula will survive this mishap.
See your file for an alternative formula , which matches the right-most 4 characters of the column headers.
Narayan
