What formula can look if two conditions, or both on two different columns are met after looking different possibilities in another column

akira0605

New Member
Joined
Mar 23, 2014
Messages
6
I have different part numbers in each cell of column A, and
I have model year of each part in Column B, and
I have part costs in column C.
How do I use a formula to look in Column A1:A2000 (part #) values such as "1350" or "1450", or "2021", and then if model year in B2:B2000>=5 years old, or cost in column C >=1000, then state "by year" if that is the case or "by cost", if that is the case, or both if both conditions are met?

I very much appreciate assistance with a formula for this.

Thank you
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,132
Office Version
  1. 2019
Platform
  1. Windows
You could try something like this. Blue cells for input and green outputs. I'm not sure how a model year is defined, so this gives the option to specify the month number where model years roll over, as well as options to adjust the criteria.
MrExcel20210225.xlsx
ABCDEF
1Part NumberModel YearPart CostToday is2/25/2021
21201910noneMonth new model year9
3220192000by costCurrent model year2021
4320142000bothModel Age (y) >=5
5420213000by costCost >=1000
6520213000by cost
762017999none
872016999by year
9820161000both
10820171000by cost
akira0605
Cell Formulas
RangeFormula
F1F1=TODAY()
F3F3=IF(MONTH(F1)<F2,YEAR(F1),YEAR(F1)+1)
D2:D10D2=IF(AND(B2<>0,C2<>0),IF(AND($F$3-B2>=$F$4,C2>=$F$5),"both",IF($F$3-B2>=$F$4,"by year",IF(C2>=$F$5,"by cost","none"))),"missing info")
 

Watch MrExcel Video

Forum statistics

Threads
1,129,299
Messages
5,635,391
Members
416,856
Latest member
silentir

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top