Combine two formulas and add a condition if True

DougStroud

Well-known Member
Joined
Aug 16, 2005
Messages
2,968
I have two formulas that I have worked out to deliver the results independently. I would like to join them together into one formula and add an If condition that if the two match then add a "+" to the end of the string.

#1 finds the base value :
=IF(ISERR(FIND("/",I2)),I2,LEFT(I2,FIND("/",I2,FIND("/",I2)-1)-1))

#2 verifies the value is a valid color:
=INDEX(M1:M41,MATCH(J2,M1:M41,0),1)

I would like to join the two formulas together and if they match then add a space, " " and a "+" to the end of the string. If there is no match then return the base value.

Hopes this is clear.

Thanks,

Doug
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,128
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
  2. Mobile
  3. Web
Hi Doug

Can this not be achieved using AND along with IF, perhaps I read wrong

KR


Dave
 

DougStroud

Well-known Member
Joined
Aug 16, 2005
Messages
2,968
Thanks Dave,
I will leave it open for the likes of the board gurus. But you are probably correct.
What I was hoping for was that when a term was found in formula #1 and matched a valid term in formula #2 this would then change the term in the applicable cell to the base value and a plus symbol all in one step.

cheers,

Doug
 

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,128
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
  2. Mobile
  3. Web
Would you prefer me to delete my input. I have see you as a much more experienced person than I but sometimes folk look for a complex answer. I'll happily delete to provide you unanswered status.
 

DougStroud

Well-known Member
Joined
Aug 16, 2005
Messages
2,968
Hi Dave,
No, Not at all.... I appreciate your post. Your answer may be completely accurate.... Just thought I would remark that if someone like Aladin, Barry or Domenic came along and saw it, maybe they would have some ideas.

Cheers,

Doug
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Your first formula can be shortened to this:
=LEFT(I2,FIND("/",I2&"/")-1)

Your second one could be reduced to:
=LOOKUP(J2,M1:M41)

or just to see if J2 is in the range of M1:M41
=COUNTIF(M1:M41,J2)>0

but I'm not sure what you are trying to return.

Can you provide a sample of what would be in I2 and J2 and what results you expect?
 

DougStroud

Well-known Member
Joined
Aug 16, 2005
Messages
2,968
Hello HP-
Your kitty looks like she got hold of the jalapeno jar... :)

What I am trying to do, is in one step compare the values from column I to values in column M. If the value in column I matches one in the table, then add a "+" sign to it, if it does not match- then leave it as is.

So Black/White in I2 would get reduced to Black.
There is a value in column M of Black, so the final return value in J2 would be "Black +"
 

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,128
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
  2. Mobile
  3. Web
Would this not be a job for conditional formatting?

Even a few helper columns in case I missed the main event.

HTH


Dave
 

DougStroud

Well-known Member
Joined
Aug 16, 2005
Messages
2,968
I can do it in Helper Columns, but trying to avoid that. Conditional formatting does not factor in here. The data is to diverse and needs to be dynamically updated.
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
How about:

=LEFT(I2,FIND("/",I2&"/")-1) & IF(COUNTIF($M$1:$M$41,LEFT(I2,FIND("/",I2&"/")-1))," +","")
 

Forum statistics

Threads
1,181,647
Messages
5,931,209
Members
436,784
Latest member
amuljono

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