If(Index(Match multiple criterias

[Even]

Hi,
I want to find and match customer with certain products. And when they match it will return either a normal % or a special % or nothing at all. There is a formula in col I, but that matches only customers that have transport. In col E I can select if it is general terms or special terms. So if it the customer and product match in columns I:M it will return either general terms or special terms.

IfIndexMatch.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
2	General terms	%	Special terms			Customer			Transport	D&O	Cyber	Crime	Art
3	Transport	4,50%	5,00%		Yes	Example 1			5,00%
4	D&O	3,50%	5,00%			Example 2
5	Cyber	3,50%	5,00%		Yes	Example 3			5,00%
6	Crime	3,50%	5,00%			Example 4
7	Art	3,50%	5,00%			Example 5
8						Example 6
9	Yes				No	Example 7			4,50%
10	No					Example 8
11						Example 9
12						Example 10
All products

Cell Formulas
Range	Formula
I3:I12	I3	=IFERROR(IF(INDEX(Sheet1!A:A,MATCH($F3,Sheet1!A:A,0))="","",IF($E3="Yes",$C$3,IF($E3="No",$B$3,""))),"")

Cells with Data Validation
Cell	Allow	Criteria
E3:E12	List	=$A$9:$A$11

IfIndexMatch.xlsx
	A	B
1	Transport customers	Type of Insurance
2	Example 1	Art
3	Example 2	Crime
4	Example 3	Transport
5	Example 4	Transport
6	Example 5	Crime
7	Example 6	D&O
8	Example 7	D&O
9	Example 8	Art
10	Example 9	Transport
11	Example 10	Transport
Sheet1

 

[Fluff]

How about

=IF(XLOOKUP($F3,Sheet1!$A$2:$A$100,Sheet1!$B$2:$B$100,"",0)=I$2,IF($E3="","",SUMIFS(IF($E3="Yes",$C$3:$C$7,$B$3:$B$7),$A$3:$A$7,I$2)),"")

 

[Even]

Thank you so much, Fluff!
It looks like it works very nicely. Except I get some irregularities. When I copy the formula into my workbook, it works on some columns but others not. That is weird. I have been looking it over for a long time now and I am getting tired. Do you know the reason for this?

 

[Fluff]

This is what I get

IfIndexMatch.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	16/05/21
2	General terms	%	Special terms			Customer			Transport	D&O	Cyber	Crime	Art
3	Transport	4.50%	5.00%		Yes	Example 1							5.00%
4	D&O	3.50%	5.00%			Example 2
5	Cyber	3.50%	5.00%		Yes	Example 3			5.00%
6	Crime	3.50%	5.00%			Example 4
7	Art	3.50%	5.00%		No	Example 5						3.50%
8						Example 6
9	Yes				No	Example 7				3.50%
10	No					Example 8
11						Example 9
12					No	Example 10			4.50%
All products

Cell Formulas
Range	Formula
A1	A1	=TODAY()
I3:M12	I3	=IF(XLOOKUP($F3,Sheet1!$A$2:$A$100,Sheet1!$B$2:$B$100,"",0)=I$2,IF($E3="","",SUMIFS(IF($E3="Yes",$C$3:$C$7,$B$3:$B$7),$A$3:$A$7,I$2)),"")

Cells with Data Validation
Cell	Allow	Criteria
E3:E12	List	=$A$9:$A$11

Is that what you would expect?

 

[Even]

I might have figured it out. Does the formula return the first value it finds for each customer? In my case one customer has multiple products. And I need to find every product for each customer.
Other than that it's exactly what I am looking for

 

[Even]

...but that reason doesn't sound right does it???

 

[jasonb75]

Lookup functions can only return 1 match. This should do what you are asking.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	General terms	%	Special terms			Customer			Transport	D&O	Cyber	Crime	Art
2	Transport	4.50%	5.00%		Yes	Example 1							3.50%
3	D&O	3.50%	5.00%			Example 2						5.00%
4	Cyber	3.50%	5.00%		Yes	Example 3			4.50%
5	Crime	3.50%	5.00%			Example 4			5.00%
6	Art	3.50%	5.00%			Example 5						5.00%
7						Example 6				5.00%
8	Yes				No	Example 7				5.00%
9	No					Example 8							5.00%
10						Example 9			5.00%
11						Example 10			5.00%
All Products

Cell Formulas
Range	Formula
I2:M11	I2	=IF(COUNTIFS(Sheet1!$A:$A,$F2,Sheet1!$B:$B,I$1)>0,VLOOKUP(I$1,$A$2:$C$6,IF($E2="Yes",2,3),0),"")

 

[Even]

Perfect!

Lookup functions can only return 1 match. This should do what you are asking.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	General terms	%	Special terms			Customer			Transport	D&O	Cyber	Crime	Art
2	Transport	4.50%	5.00%		Yes	Example 1							3.50%
3	D&O	3.50%	5.00%			Example 2						5.00%
4	Cyber	3.50%	5.00%		Yes	Example 3			4.50%
5	Crime	3.50%	5.00%			Example 4			5.00%
6	Art	3.50%	5.00%			Example 5						5.00%
7						Example 6				5.00%
8	Yes				No	Example 7				5.00%
9	No					Example 8							5.00%
10						Example 9			5.00%
11						Example 10			5.00%
All Products

Cell Formulas
Range	Formula
I2:M11	I2	=IF(COUNTIFS(Sheet1!$A:$A,$F2,Sheet1!$B:$B,I$1)>0,VLOOKUP(I$1,$A$2:$C$6,IF($E2="Yes",2,3),0),"")