IF multiple criteria

[CassieL]

Hi everyone,
I have the following table:

Type	Min	Max	ID	% Fees
Office	0	50	1	3%
Office	51	150	2	4%
Office	151	300	3	5%
Retail	o	150	4	3.5%
Retail	151	500	5	4.5%
Retail	501	1000	6	8%
Other	0	1500	7	5%

<tbody>
</tbody>

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

Row\Col	A​	B​	C​	D​	E​	F​	G​	H​
1​	Type	Min	Max	ID	% Fees		retail	600​
2​	Office	0​	50​	1​	3%​
3​	Office	51​	150​	2​	4%​		6​
4​	Office	151​	300​	3​	5%​
5​	Retail	0​	150​	4​	3.50%​
6​	Retail	151​	500​	5​	4.50%​
7​	Retail	501​	1000​	6​	8%​
8​	Other	0​	1500​	7​	5%​

<tbody>
</tbody>

 

[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