If multiple conditions

[Dexir]

If multiple conditions.xlsx
	B	C	D	E
2		80%	90%	100%
3	Primary	10	20	30
4	Secondary	100	200	300
5	General	1000	2000	3000
6
7
8		Secondary	80%	100
Sheet1

Cell Formulas
Range	Formula
E8	E8	=IF(C8=B3,IF(D8=C2,C3,IF(D8=D2,D3,IF(D8=E2,E3,0))),IF(C8=B4,IF(D8=C2,C4,IF(D8=D2,D4,IF(D8=E2,E4))),IF(C8=B5,IF(D8=C2,C5,IF(D8=D2,D5,IF(D8=E2,E5))))))

Cells with Data Validation
Cell	Allow	Criteria
C8	List	=$B$3:$B$5

I have this sheet, where i am using if multiple conditions formula, this is lengthy formula, is there any alternate formula for this ?

 

[GraH]

Seems you are after an exact 2-way lookup.

Book1
	B	C	D	E
2		80%	90%	100%
3	Primary	10	20	30
4	Secondary	100	200	300
5	General	1000	2000	3000
6
7		Primary	0,9	20
8				20
Sheet1

Cell Formulas
Range	Formula
E7	E7	=INDEX(C3:E5,XMATCH(C7,B3:B5),XMATCH(D7,C2:E2,))
E8	E8	=XLOOKUP(D7,C2:E2,XLOOKUP(C7,B3:B5,C3:E5))

 

[Dexir]

Thanks alot, but sorry to say that i am using Office 2016 in office. Xlookup and Xmatch doesn't working.

 

[JEC]

How about

Book1
	A	B	C	D
1		80%	90%	100%
2	Primary	10	20	30
3	Secondary	100	200	300
4	General	1000	2000	3000
5
6
7		Secondary	80%	100
Sheet2

Cell Formulas
Range	Formula
D7	D7	=SUMPRODUCT((B1:D1=C7)*(A2:A4=B7)*(B2:D4))

 

[Fluff]

How about

=INDEX(C3:E5,MATCH(C8,B3:B5,0),MATCH(D8,C2:E2,0))

 

[Dexir]

Thanks alot., Mr. JEC & Mr. Fluff. it is working perfect.

 

[Fluff]

Glad we could help & thanks for the feedback.