Instead of a bunch of nested IF statements...

[StuartM1]

Hi all,

What’s the best way to test each row in a large dataset tosee if it meets three criteria?
If cell A2 is “Blue” and the Expiration Date in B2 is 5/17/18, and C2 is a number, I need cell D2 to multiply the number in C2 by .3 per the table below.

And so on and so on for each row.

What’s the correct way to do this without having a huge combinationof IF statements?


Here’s a copy of the table with test criteria.

Color Code	Expires after	Expires before	Discount	Multiplier
Blue	12/31/2000	8/15/2018	100	0.1
Blue	12/31/2000	6/30/2018	100	0.3
Red	12/31/2000	6/30/2018	100	0.2
Red	12/31/2000	6/30/2018	100	0.25

<colgroup><col width="88" style="width: 66pt; mso-width-source: userset; mso-width-alt: 3218;"> <col width="109" style="width: 82pt; mso-width-source: userset; mso-width-alt: 3986;"> <col width="129" style="width: 97pt; mso-width-source: userset; mso-width-alt: 4717;"> <col width="86" style="width: 65pt; mso-width-source: userset; mso-width-alt: 3145;" span="2"> <tbody> </tbody>

Any ideas would be appreciated!!
Thanks!!!

 

[Aladin Akyurek]

Is this sample data? If it's, what are your criteria for this data and the expected outcome?

 

[StuartM1]

Maybe this is a better visual:

Color	Expires	Number	Product
Blue	5/17/2018	600	Multiply 600 * .3 from table below
Color Code	Expires after	Expires before	Multiplier
Blue	12/31/2000	8/15/2018	0.1
Blue	12/31/2000	6/30/2018	0.3
Red	12/31/2000	6/30/2018	0.2
Red	12/31/2000	6/30/2018	0.25

<tbody>
</tbody>

 

[Aladin Akyurek]

Thanks for the clarification...

Book1
	A	B	C	D
1	Color	Expires	Number	Product
2	Blue	5/17/2018	600	60
3
4
5
6
7
8	Color Code	Expires after	Expires before	Multiplier
9	Blue	12/31/2000	8/15/2018	0.1
10	Blue	12/31/2000	6/30/2018	0.3
11	Red	12/31/2000	6/30/2018	0.2
12	Red	12/31/2000	6/30/2018	0.25
Sheet1

In D2 control+shift+enter, not just enter:

=IFERROR(INDEX($D$9:$D$12,MATCH(A2,IF(B2>=$B$9:$B$12,IF(B2<=$C$9:$C$12,$A$9:$A$12)),0))*C2,"not available")

 

[StuartM1]

Perfect. Thanks Aladin!!

 

[Aladin Akyurek]

Perfect. Thanks Aladin!!

You are welcome.