Index Match Multiple conditions

[LectorFiel]

Dear Experts,

I am using a function Index and Match to get a value from a Table that contains some Fees.
In order to identify the right Fee, I am looking using three criteria. When I used two criteria it works, partially because I do not get error but the result is not correct totally , later I added the third one try to get the right data, but I got an Error #N/A.

Table1

Client Start date End Date Fee1
AAA 1/1/2016 12/31/2016 1
AAA 1/1/2017 12/31/2017 1.5
AAA 1/1/2018 12/31/2018 2

In Another Sheet, I have Client and Date, on this way.


Client 1/1/2016 1/1/2016 2/1/2016 3/1/2016 1/1/2017 2/1/2018
AAA X X X X
AAA
AAA
BBB
BBB
CCC

The function I am using is this : =INDEX(Table1[Fee1],MATCH(1,(Table1[Client]=$A2)*(Table1[Start Date]>=B$1),0)) and press Ctrl + Shift + Enter
This brings the first data correct when Client and Date are the same, but when date is different results belong to the next range. Which It is Wrong.

I Thought that if I add a third criteria, wondering if date is between Start Date and End Date, will help me to get the right data, but I got an Error.

=INDEX(Table1[Fee1],MATCH(1,(Table1[Client]=$A2)*(Table1[Start Date]>=B$1)*(Table1[End Date]>B$1),0)) and [FONT=arial, sans-serif]press Ctrl + Shift + Enter[/FONT]
[FONT=arial, sans-serif]I got and Error #N/A.[/FONT]

[FONT=arial, sans-serif]I appreciate your help in this matter, looks that it is easy but I really spend a lot of hours and nothing.[/FONT]

[FONT=arial, sans-serif]Thanks for your collaboration.[/FONT]

 

[Nishant94]

Excel 2016
	A	B	C	D	E	F
1	Client	Start Date	End Date	Fee1
2	AAA	01/01/2016	12/31/2016	1
3	AAA	01/01/2017	12/31/2017	1.5
4	AAA	01/01/2018	12/31/2018	2
5	BBB	01/01/2016	12/31/2016	3
6	BBB	01/01/2018	12/31/2018	4.5
7	BBB	01/01/2017	12/31/2017	10
8
9
10	Client	01/01/2016	01/02/2016	03/01/2016	01/01/2017	02/01/2018
11	AAA	1	1	1	1.5	2
12	AAA	1	1	1	1.5	2
13	AAA	1	1	1	1.5	2
14	BBB	3	3	3	10	4.5
15	BBB	3	3	3	10	4.5
16
Sheet11

Cell Formulas
Range	Formula
B11	{=INDEX(Table1[Fee1],MATCH(1,(Table1[Start Date]<=B$10)*(Table1[End Date]>=B$10)*(Table1[Client]=$A11),0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Nishant94]

Make sure your Lookup value matches with the table. Make sure you don't have extra spaces in lookup value like "AAA" should not be "AAA ".
Press F2 and check the lookup value.

 

[LectorFiel]

Make sure your Lookup value matches with the table. Make sure you don't have extra spaces in lookup value like "AAA" should not be "AAA ".
Press F2 and check the lookup value.

Dear Nishant94, It works very very well. Thank you very much, I can see now what was my mistake, even knowing what the function can do, my mistake was the way I set the parameters. I apply your formula and it works, excellent.

Thanks again.

 

[Oeldere]

or with a helpcolumn and Vlookup.

make sure you sort the date A - Z (see the orange cells)

Book1
	A	B	C	D	E	F	G
1	helpcell	Client	Start Date	End Date	Fee1
2	AAA42370	AAA	1-1-2016	12/31/2016	1
3	AAA42736	AAA	1-1-2017	12/31/2017	1,5
4	AAA43101	AAA	1-1-2018	12/31/2018	2
5	BBB42370	BBB	1-1-2016	12/31/2016	3
6	BBB42736	BBB	1-1-2017	12/31/2017	10
7	BBB43101	BBB	1-1-2018	12/31/2018	4,5
8
9
10
11		Client	1-1-2016	1-2-2016	3-1-2016	1-1-2017	2-1-2018
12		AAA	1	1	1	1,5	2
13		AAA	1	1	1	1,5	2
14		AAA	1	1	1	1,5	2
15		BBB	3	3	3	10	4,5
16		BBB	3	3	3	10	4,5
17
Blad25

Cell Formulas
Range	Formula
A2	=B2&C2
C12	=VLOOKUP($B12&C$11,$A$2:$E$7,5,1)
D12	=VLOOKUP($B12&D$11,$A$2:$E$7,5,1)
E12	=VLOOKUP($B12&E$11,$A$2:$E$7,5,1)
F12	=VLOOKUP($B12&F$11,$A$2:$E$7,5,1)
G12	=VLOOKUP($B12&G$11,$A$2:$E$7,5,1)