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.

[TABLE="width: 375"]
<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>[TR]
[TD="width: 88, bgcolor: #B4C6E7"]Color Code[/TD]
[TD="width: 109, bgcolor: #B4C6E7"]Expires after[/TD]
[TD="width: 129, bgcolor: #B4C6E7"]Expires before[/TD]
[TD="width: 86, bgcolor: #B4C6E7"]Discount[/TD]
[TD="width: 86, bgcolor: #B4C6E7"]Multiplier[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Blue[/TD]
[TD="bgcolor: transparent, align: right"]12/31/2000[/TD]
[TD="bgcolor: transparent, align: right"]8/15/2018[/TD]
[TD="bgcolor: transparent, align: right"]100[/TD]
[TD="bgcolor: transparent, align: right"]0.1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Blue[/TD]
[TD="bgcolor: transparent, align: right"]12/31/2000[/TD]
[TD="bgcolor: transparent, align: right"]6/30/2018[/TD]
[TD="bgcolor: transparent, align: right"]100[/TD]
[TD="bgcolor: transparent, align: right"]0.3[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Red[/TD]
[TD="bgcolor: transparent, align: right"]12/31/2000[/TD]
[TD="bgcolor: transparent, align: right"]6/30/2018[/TD]
[TD="bgcolor: transparent, align: right"]100[/TD]
[TD="bgcolor: transparent, align: right"]0.2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Red[/TD]
[TD="bgcolor: transparent, align: right"]12/31/2000[/TD]
[TD="bgcolor: transparent, align: right"]6/30/2018[/TD]
[TD="bgcolor: transparent, align: right"]100[/TD]
[TD="bgcolor: transparent, align: right"]0.25[/TD]
[/TR]
</tbody>[/TABLE]





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:

[TABLE="width: 584"]
<tbody>[TR]
[TD]Color[/TD]
[TD]Expires[/TD]
[TD]Number[/TD]
[TD]Product[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Blue[/TD]
[TD]5/17/2018[/TD]
[TD]600[/TD]
[TD="colspan: 3"]Multiply 600 * .3 from table below[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Color Code[/TD]
[TD]Expires after[/TD]
[TD]Expires before[/TD]
[TD]Multiplier[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Blue[/TD]
[TD="align: right"]12/31/2000[/TD]
[TD="align: right"]8/15/2018[/TD]
[TD="align: right"]0.1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Blue[/TD]
[TD="align: right"]12/31/2000[/TD]
[TD="align: right"]6/30/2018[/TD]
[TD="align: right"]0.3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Red[/TD]
[TD="align: right"]12/31/2000[/TD]
[TD="align: right"]6/30/2018[/TD]
[TD="align: right"]0.2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Red[/TD]
[TD="align: right"]12/31/2000[/TD]
[TD="align: right"]6/30/2018[/TD]
[TD="align: right"]0.25[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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.