two way lookup

[Srinivas]

Sir,

I have two files one is payroll and other is advmaster
in adv master data is like this

emp name emp no. adv jan feb mar apr may......dec
jack k-123 5000 500 500 650 400
jill k-124 2000 100 100 100

in the payroll file

month : jan

emp name emp no pay ot total adv ded net
jack k-123 15000 15000
jill k-124 10000 10000

In the adv coloum i want a formula which should match the emp no of emp in
payroll and emp no of emp in adv master and parallely it should check the
month of payroll with month of adv master and to take that value to deduct.
can any one help me in this

regards

srinivas

 

[Roger Govier]

Hi

Assuming in the Master file, the header is row 1, starting at A1 and in the
Payroll file, the header is row 2 starting at A2.
Also, in the Payroll file, A1 has the title Month, and B1 has the month
Value Jan

=INDEX(advmaster!$A$1:$O$100,MATCH($B3,advmaster!$B$1:$B$100,0),
MATCH($B$1,advmaster!$A$1:$O$1,0))

 

[Srinivas]

THANK YOU VERY MUCH SIR
--
srinivas

Hi

Assuming in the Master file, the header is row 1, starting at A1 and in the
Payroll file, the header is row 2 starting at A2.
Also, in the Payroll file, A1 has the title Month, and B1 has the month
Value Jan

=INDEX(advmaster!$A$1:$O$100,MATCH($B3,advmaster!$B$1:$B$100,0),
MATCH($B$1,advmaster!$A$1:$O$1,0))