# Combine two formulas and add a condition if True

#### DougStroud

##### Well-known Member
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

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Hi Doug

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

KR

Dave

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

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.

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

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?

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 +"

Would this not be a job for conditional formatting?

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

HTH

Dave

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.

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

Replies
2
Views
127
Replies
3
Views
177
Replies
11
Views
444
Replies
10
Views
326
Replies
2
Views
244

1,220,965
Messages
6,157,122
Members
451,399
Latest member
alchavar

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

### Which adblocker are you using?

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

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