IF multiple criteria

[CassieL]

Hi everyone,
I have the following table:
[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]Type[/TD]
[TD]Min[/TD]
[TD]Max[/TD]
[TD]ID[/TD]
[TD]% Fees[/TD]
[/TR]
[TR]
[TD]Office[/TD]
[TD]0[/TD]
[TD]50[/TD]
[TD]1[/TD]
[TD]3%[/TD]
[/TR]
[TR]
[TD]Office[/TD]
[TD]51[/TD]
[TD]150[/TD]
[TD]2[/TD]
[TD]4%[/TD]
[/TR]
[TR]
[TD]Office[/TD]
[TD]151[/TD]
[TD]300[/TD]
[TD]3[/TD]
[TD]5%[/TD]
[/TR]
[TR]
[TD]Retail[/TD]
[TD]o[/TD]
[TD]150[/TD]
[TD]4[/TD]
[TD]3.5%[/TD]
[/TR]
[TR]
[TD]Retail[/TD]
[TD]151[/TD]
[TD]500[/TD]
[TD]5[/TD]
[TD]4.5%[/TD]
[/TR]
[TR]
[TD]Retail[/TD]
[TD]501[/TD]
[TD]1000[/TD]
[TD]6[/TD]
[TD]8%[/TD]
[/TR]
[TR]
[TD]Other[/TD]
[TD]0[/TD]
[TD]1500[/TD]
[TD]7[/TD]
[TD]5%[/TD]
[/TR]
</tbody>[/TABLE]

I have : Office type with $150, the formula should returns ID 2.
Retail type with $600, the formula will returns ID 6

How can I execute with if function with one criteria of type and if between?

Many thanks!

 

[Weazel]

maybe something like...

G3=INDEX(D2:D8,MATCH(H1,IF(A2:A8=G1,B2:B8))) control shift enter

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[TD]

H​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]Type[/TD]
[TD]Min[/TD]
[TD]Max[/TD]
[TD]ID[/TD]
[TD]% Fees[/TD]
[TD][/TD]
[TD]retail[/TD]
[TD]

600​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]Office[/TD]
[TD]

0​

[/TD]
[TD]

50​

[/TD]
[TD]

1​

[/TD]
[TD]

3%​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]Office[/TD]
[TD]

51​

[/TD]
[TD]

150​

[/TD]
[TD]

2​

[/TD]
[TD]

4%​

[/TD]
[TD][/TD]
[TD]

6​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]Office[/TD]
[TD]

151​

[/TD]
[TD]

300​

[/TD]
[TD]

3​

[/TD]
[TD]

5%​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]Retail[/TD]
[TD]

0​

[/TD]
[TD]

150​

[/TD]
[TD]

4​

[/TD]
[TD]

3.50%​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]Retail[/TD]
[TD]

151​

[/TD]
[TD]

500​

[/TD]
[TD]

5​

[/TD]
[TD]

4.50%​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]Retail[/TD]
[TD]

501​

[/TD]
[TD]

1000​

[/TD]
[TD]

6​

[/TD]
[TD]

8%​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]Other[/TD]
[TD]

0​

[/TD]
[TD]

1500​

[/TD]
[TD]

7​

[/TD]
[TD]

5%​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Tetra201]

For Office $150 =SUMPRODUCT(($A$1:$A$8="Office")*($B$1:$B$8 <=150)*($C$1:$C$8 >=150),($D$1:$D$8))
For Retail $600 =SUMPRODUCT(($A$1:$A$8="Retail")*($B$1:$B$8 <=600)*($C$1:$C$8 >=600),($D$1:$D$8))

 

[CassieL]

Hi Weazel,

Thanks for your reply. Sorry that if I didn't explain well. The reason that it returns ID 6 is because the 600 is between 501 and 1000. I think you are missing the between formula? Please advise.

 

[Weazel]

no, the match takes care of the between by doing an approximate match

 

[CassieL]

Hi Tetra 201

You might not remember me, you have helped me in the past. And your formula above work perfectly! I just need to assigned the "retail/office" and "150/600" value in the respective cell. Thank you so much! Hope you have a great weekend!

 

[CassieL]

Hi Weazel,

Im getting error return. your formula does not include column C though. I am willing to try your formula as well!

 

[Tetra201]

You are most welcome.

 

[Weazel]

the formula doesn't need to include column C because column B is sorted ascending and the match is doing an approximate match based on the values in G1 and H1