Multiple IF and/or LOOKUP

[Posthuman]

Dear Excel Gurus!

Attached you may find my sample excel file. I tried to explain details there. Can you please check and write me a formula? Simply, it's a multiple decisions based on provided data.

Thank you!

 

[Luke M]

Welcome to the forum Posthuman!

Formula in E3 would be:
=SUMIFS(INDEX($L$3:$N$33,,MATCH(D3,{0,10001,25001})),$G$3:$G$33,A3,$H$3:$H$33,"<="&B3,$I$3:$I$33,">="&B3,$J$3:$J$33,"<="&C3,$K$3:$K$33,">="&C3)

 

[eibi]

I've composed a formula similar to Luke's (see attached file):

=INDEX($L$3:$N$33,SUMPRODUCT(($G$3:$G$33=A3)*($H$3:$H$33<=B3)*($I$3:$I$33>=B3)*($J$3:$J$33<=C3)*($K$3:$K$33>=C3)*ROW($L$3:$N$33))-2,MATCH(D3,{0,10001,25001}))

Note: In the third row of sample data you provided, both Luke and I are returning a different result than you predicted.

All best.

 

[Posthuman]

Dear Luke,

First of all thanks for warm welcome!

Still I couldn't make it run, I think it's because of localization settings or something like that. When I copy/paste your formula excel warns me. Then I replaced all , with ; but this time it returns with #REF!

I am not so good with excel (besides painting cells Can you give me a hint what to do? or could you put your formula in E3 and upload here?

Thanks in advance.

P.S. I re-uploaded file in case you may want to check it.

 

[Posthuman]

Hello Eibi,

Yeah, I realized that by your warning. You are right, it's my mistake.

I checked your file and it works pretty good!

Thanks a lot!

 

[Luke M]

May be solved, but just in case...