Edit Or Combining Formula's

Novman

New Member
Joined
Feb 19, 2023
Messages
20
Office Version
  1. 365
Platform
  1. Windows
Table Help.xlsm
BCDEFGHIJKLMNOPQRST
2WorkingWorkingNo Formula
3CARLIGHT BLUE2385 CARLIGHT blue2385 CARLIGHT blue55
4CARgrey88CARgrey8 CARgrey8
5CARwhite2013 CARwhite20133CARBLUE2013
6carblack6324 carblack63244carblack6324
7cargrey11cargrey1 carDARK BLUE11
8976
9=IF(AND(B3="CAR",ISERROR(SEARCH("BLUE",C3)))*(D3=""),E3," ")=IF(AND(I3="car",ISERROR(SEARCH("blue",J3)))*(K3<>""),L3,"")
10
11Above is a short table with formula in green cellAbove is a short table were I would like to put a formulaAbove is a short table were I would like to put a formula
123 checks and 1 returnColumn M is what I would like the formula to returnColumn M is what I would like the formula to return
13Check 1. does B3:B7 = car if I3:I7 = "car" if O3:O7 = "car"
14Check 2. does C3:C7 not have word blueDoes J3:J7 not contain word "blue"does P3:P7 have word blue
15Check 3. does D3:D7 not have any textDoes K3:K7 have any textDoes Q3:Q7 not have any text
16Retune. If all criteria is met return E3:E7 in F3:F7If all criteria is met return L3:L7 in M3:M7If all criteria is met return R3:R7 in S3:S7
17
18The differences between the 2 formulas would beI have tried combining the formula's from other table's
19theses parts of the formulato find the formula for the above table but I cant get it right
Sheet1
Cell Formulas
RangeFormula
F3:F7F3=IF(AND(B3="CAR",ISERROR(SEARCH("BLUE",C3)))*(D3=""),E3," ")
F8,S8,M8F8=SUM(F3:F7)
M3:M7M3=IF(AND(I3="car",ISERROR(SEARCH("blue",J3)))*(K3<>""),L3,"")
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
try this
Excel Formula:
=IF(AND(O3="car",NOT(ISERROR(SEARCH("blue",P3))))*(Q3=""),R3,"")
-----------------

Book1
BCDEFGHIJKLMNOPQRST
1
2WorkingWorkingNo Formula
3CARLIGHT BLUE2385 CARLIGHT blue2385 CARLIGHT blue55
4CARgrey88CARgrey8 CARgrey8 
5CARwhite2013 CARwhite20133CARBLUE2013 
6carblack6324 carblack63244carblack6324 
7cargrey11cargrey1 carDARK BLUE11
8976
9  
10
11Above is a short table with formula in green cellAbove is a short table were I would like to put a formulaAbove is a short table were I would like to put a formula
123 checks and 1 returnColumn M is what I would like the formula to returnColumn M is what I would like the formula to return
13Check 1. does B3:B7 = car if I3:I7 = "car" if O3:O7 = "car"
14Check 2. does C3:C7 not have word blueDoes J3:J7 not contain word "blue"does P3:P7 have word blue
15Check 3. does D3:D7 not have any textDoes K3:K7 have any textDoes Q3:Q7 not have any text
16Retune. If all criteria is met return E3:E7 in F3:F7If all criteria is met return L3:L7 in M3:M7If all criteria is met return R3:R7 in S3:S7
17
18The differences between the 2 formulas would beI have tried combining the formula's from other table's
19theses parts of the formulato find the formula for the above table but I cant get it right
Sheet1
Cell Formulas
RangeFormula
F3:F7F3=IF(AND(B3="CAR",ISERROR(SEARCH("BLUE",C3)))*(D3=""),E3," ")
F8,S8,M8F8=SUM(F3:F7)
M3:M7M3=IF(AND(I3="car",ISERROR(SEARCH("blue",J3)))*(K3<>""),L3,"")
S3:S7S3=IF(AND(O3="car",NOT(ISERROR(SEARCH("blue",P3))))*(Q3=""),R3,"")
B9B9=IF(AND(B3="CAR",ISERROR(SEARCH("BLUE",C3)))*(D3=""),E3," ")
I9I9=IF(AND(I3="car",ISERROR(SEARCH("blue",J3)))*(K3<>""),L3,"")
 
Upvote 0
Solution

Forum statistics

Threads
1,223,098
Messages
6,170,099
Members
452,301
Latest member
QualityAssurance

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
Back
Top