How do I take the lookup value from different sheets in a workbook and show it corresponding results. Here is the link to a sample file
Basically, the first three sheets contain a table with headers ID, Name, Basic, DA, HRA, Gross, Incentive with different values for each row in the column. Now in sheet4 when I put a value for the ID column the vlookup should check in which sheet this value is present and according to that it should show its Gross column value and so on..
For example if I enter a value of C005 in the ID field in sheet4, the result in the Gross field should be 6420, if I enter a value of A002 in the ID field in sheet4, the result in the Gross field should be 8774 and so on...
I tried to do something like =IF(LEFT(A2,1)=A,VLOOKUP($A$2,Sheet3!$A$1:$G$13,6,0),IF(LEFT(A2,1)=B,VLOOKUP($A$2,Sheet2!$A$1:$G$13,6,0)),IF(LEFT(A2,1)=C,VLOOKUP($A$2,Sheet1!$A$1:$G$13,6,0)))
but it is showing error and most importantly it is very long and complicated.
Can anyone help?
