Combine two formulas and add a condition if True

[DougStroud]

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

 

[dave3009]

Hi Doug

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

KR


Dave

 

[DougStroud]

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]

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]

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]

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]

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]

Would this not be a job for conditional formatting?

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

HTH


Dave

 

[DougStroud]

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]

How about:

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