Multiple conditions

[Zubair]

Hi Experts,

Given below my excel sheet,

I want formula in cell B5 to bring figure based on the selection of B1:B3 from the array E5:K7, with flexibility of changing cell A5, like currently appearing sales, if I put Cost in cell A5 it should give figure 120.

 

[Sergius]

Hello! Try it

=INDEX(E5:K7,MATCH(1,(A5=D5:D7)*(B1=E1:K1)*(B2=E2:K2)*(B3=E3:K3),0))

 

[Zubair]

Hi Sergius,
Many thanks but its giving #N/A

 

[Peter_SSs]

I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

Does this do what you want?

25 01 22.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1		2024			2023	2023	2024	2024	2023	2024	2024
2		May			Jan	Jan	May	Feb	Mar	Apr	May
3		Scenario 1			Scenario 1	Scenario 2	Scenario 1	Scenario 2	Scenario 5	Scenario 2	Scenario 2
4
5	Sales	365		Sales	322	718	365	556	485	140	159
6				Cost	128	224	463	763	177	741	905
7				Profit/(Loss)	102	251	173	828	853	559	962
Lookup Value

Cell Formulas
Range	Formula
B5	B5	=SUMPRODUCT((D5:D7=A5)*(E1:K1=B1)*(E2:K2=B2)*(E3:K3=B3),E5:K7)

 

[Zubair]

Yes thanks that's what I needed.

for XL2BB I tried number of time but not able to bring it for use. Will keep try.

Thanks again

 

[Peter_SSs]

Yes thanks that's what I needed.

You're welcome. Thanks for the follow-up.

for XL2BB I tried number of time but not able to bring it for use.

What goes wrong when you try to use it?