Vlookup for two columns

[etuskan]

I have following tables:

Employee #	Annual Leave Date
1	07.07.2010
1	06.06.2011
1	08.08.2012
2	08.08.2009
2	08.08.2010
2	08.08.2011

<tbody>
</tbody>

Employee #	Paid Leave Date	Days
1	5.5.2010	14
1	5.5.2011	18
1	5.5.2012	20
2	6.6.2010	14
2	6.6.2011	18

<tbody>
</tbody>

I would like to create a formula that looks up for Employee # and date (first two columns) from the first table and give the results from the second table.
Vlookup(Employee#1 and 07.07.2010) should give me 14
Vlookup(Employee#1 and 06.06.2011) should give me 18
Vlookup(Employee#1 and 08.08.2012) should give me 20
Vlookup(Employee#2 and 08.08.2010) should give me 14
Vlookup(Employee#2 and 08.08.2011) should give me 18

I am looking forward for your support
Thank you
Cheers!

 

[mart37]

i think your data is wrong
a date with . is for excel a text!

why table 1?
table 2 give the answers

 

[mart37]

Book1
	A	B	C	D	E	F	G
10	Employee #	Paid Leave Date	Days
11	1	5-5-2010	14		1	7-7-2010	14
12	1	5-5-2011	18		1	6-6-2011	18
13	1	5-5-2012	20		1	8-8-2012	20
14	2	6-6-2010	14		2	8-8-2010	14
15	2	5-5-2012	18		2	8-8-2012	18
Blad1

Cell Formulas
Range	Formula
G11	=SUMPRODUCT((LARGE(($A$11:$A$15=E11)*($B$11:$B$15<=F11)*($B$11:$B$15),1)=$B$11:$B$15)*($A$11:$A$15=E11)*$C$11:$C$15)
G12	=SUMPRODUCT((LARGE(($A$11:$A$15=E12)*($B$11:$B$15<=F12)*($B$11:$B$15),1)=$B$11:$B$15)*($A$11:$A$15=E12)*$C$11:$C$15)
G13	=SUMPRODUCT((LARGE(($A$11:$A$15=E13)*($B$11:$B$15<=F13)*($B$11:$B$15),1)=$B$11:$B$15)*($A$11:$A$15=E13)*$C$11:$C$15)
G14	=SUMPRODUCT((LARGE(($A$11:$A$15=E14)*($B$11:$B$15<=F14)*($B$11:$B$15),1)=$B$11:$B$15)*($A$11:$A$15=E14)*$C$11:$C$15)
G15	=SUMPRODUCT((LARGE(($A$11:$A$15=E15)*($B$11:$B$15<=F15)*($B$11:$B$15),1)=$B$11:$B$15)*($A$11:$A$15=E15)*$C$11:$C$15)

 

[Aladin Akyurek]

Employee #	Annual Leave Date
1	7/7/2010	14
1	6/6/2011	18
1	8/8/2012	20
2	8/8/2009	not available
2	8/8/2010	14
2	8/8/2011	18
Employee #	Paid Leave Date	Days
1	5/5/2010	14
1	5/5/2011	18
1	5/5/2012	20
2	6/6/2010	14
2	6/6/2011	18

<tbody>
</tbody>

In C2 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($C$10:$C$14,MATCH(B2,IF($A$10:$A$14=A2,$B$10:$B$14),1)),"not available")

 

[kvsrinivasamurthy]

A10:C15 is second table.

Formula in D2, then drag down

=SUMPRODUCT(($A$11:$A$15=A2)*(YEAR($B$11:$B$15)=YEAR(B2))*($C$11:$C$15))