vlookup referenced arrays

[Shaun]

Hi all


I had an interesting issue crop up today to which I am trying to resolve.


I have a list of wage payments made to employees net of PAYGW (Tax Withheld). I need to reconcile this back to gross wages.


I have compiled two additional worksheets which contain Tax tables and some calculations to pull the necessary answers from.


I am currently using the formula

=VLOOKUP(C14,'Tax Table 2011-12'!$B$5:$C$2504,2,FALSE). I would like to be able to make the [code]'Tax Table 2011-12'!$B$5:$C$2504

part "changeable". For that purpose I have prepared a couple of lists, a couple of vlookup's and a single =Concatenate() to join the the vlookup's together. I have then changed the array to reference the concatenate cell. Doesn't work. It also doesn't work when using T()[/code].


I have tried to get my head around Formula Forensics No. 003 which Hui has referenced previously but I am struggling to follow.


The file can be found at:

https://rapidshare.com/files/3156845803/PAYGW.xlsx


Any assistance would be greatly appreciated.


Cheers


Shaun

 

[NARAYANK991]

Hi Shaun ,


Try this for the first one :


=VLOOKUP(C14,INDIRECT(VLOOKUP($C$8,$E$4:$F$5,2,FALSE)&VLOOKUP($C$9,$J$3:$N$5,5,FALSE)),2,FALSE)


This will lookup the nett pay in C9 , in the appropriate table , in the appropriate sheet tab , based on the selections made in C8 and C9.


Narayan

 

[Shaun]

Hi Narayan


Thank you, it works perfectly.


Now I have to learn about INDIRECT(). I have read previously that the & works in the same manner as CONCATENATE().


I don't know if you too a look, but are you able to explain why my attempts kept failing, what was causing it not to work?


Cheers


Shaun

 

[NARAYANK991]

Hi Shaun ,


The problem was :


The absence of INDIRECT and the CONCATENATE / & ; the VLOOKUP was returning a string for the tab name ; to this , first you need to concatenate the range reference , which can only come from a second VLOOKUP. So once the two VLOOKUP results were concatenated , you would end up with a string specifying a complete reference , inclusive of the tab name and the range address.


By wrapping an INDIRECT around this string , you convert it into a real range reference ; let us consider an example :


Suppose A1 contains the numeric value 5. In C1 , if you put =A1 , you get the value which is in cell A1 i.e. 5.


Now suppose you put =INDIRECT(B1) in C1 , you will see a #REF! error in C1 ; this is because B1 is blank. Now put in a cell address such as A1 or $A$1 in B1 ; immediately , cell C1 will display 5. The contents of B1 are text ; however , the INDIRECT function expects a text parameter , and interprets it as a cell address ; it therefore retrieves the value in the address A1 , which is 5.


You can also put in a formula such as =INDIRECT("A1") in C1 ; it will again show 5.


Now , if you add a sheet tab to the cell address , it can become something like :


=INDIRECT("'Just another sheet tab'!$C$5")


The single quotes around the tab name are because the tab name contains space characters.


If you reference another workbook , follow the regular syntax , and use :


=INDIRECT("'[Yet another workbook.xlsx]Just another sheet tab'!$C$5")


The only thing to remember is that the parameter for the INDIRECT function has to be a text string , which can evaluate to a valid worksheet cell address / range.


Just play around with various constructs , and you will soon be comfortable.


Narayan