# How to compare non-similar words (excel formula)

I work with Excel 2003

I have a Teams (example: G50) and each team there are some Groups (example: 70-1, 70-2, etc.).

I want to set a condition that determined the following rule (pic example):

if G50 = 70-1 or 70-2 or 70-3 etc. (up to 5 Groups)

if G51 = 71-1 or 71-2 or 71-3 etc. (up to 5 Groups)

The answer should be: 1-TRUE , 0-FALSE

examples:

1. G50=70-5 , 1-TRUE (because the GROUP 70-5 belongs to TEAM G50)
2. G50=71-1 , 0-FALSE (because the GROUP 71-1 does NOT belongs to TEAM G50)
3. G52=70-1 , 0-FALSE (because the TEAM G52 does NOT belongs to GROUP 70-1)

Try this Array Formula. You have to press CTRL+SHIFT+ENTER

`=IF(SUMPRODUCT((\$A\$1:\$A\$11=A1)*(LEFT(\$B\$1:\$B\$11,FIND("-",B1,1)-1)=LEFT(B1,FIND("-",B1,1)-1)))>1,1,0)`

I created Row 10 and row 11 data to demonstrate that

1. The formula will work even if your data is not sorted

2. Different data format (Should have `-` though)

FOLLOWUP

Array Formulas: See THIS

Explaining the formula

`FIND("-",B1,1)`

`FIND` locates one text string within a second text string, and returns the number of the starting position of the first text string from the first character of the second text string. In the above formula I am trying to find "-" in `B1`

I then use `LEFT` to return the characters from the beginning of the string up till `-`.

Then I match it with the rest of the column to find a match. I then use `SUMPRODUCT` to return the sum of all the matches.

To understand how each formula works independently, I would recommend checking Excel's inbuilt help which explains each function with examples.

HTH

• Dear Siddharth Rout, I don't understand your answer & I tried it anyway and It doesn't work :(
– Etan
Apr 21 '13 at 5:16
• Which part you didn't understand? Apr 21 '13 at 6:33
• I didn't understand the: ctrl+shift+enter , and your formula - it doesn't work :(
– Etan
Apr 22 '13 at 6:43
• But which part? Apr 22 '13 at 6:45
• I didn't understand the: ctrl+shift+enter , and your formula - it doesn't work :(
– Etan
Apr 22 '13 at 6:45

Assuming your data given in the picture starts from A2 and the result you expect in C2, use the function `=IF(VALUE(RIGHT(A2,2))+20=VALUE(LEFT(B2,2)),1,0)` Here is the explanation:

1. RIGHT(A2,2))+20 - checks two characters from right in cell A2 which is 50 and adds 20 to it making it 70
2. LEFT(B2,2) - checks two characters from left in cell B2 which is 70
3. VALUE - The excel returns the numbers 50+20 = 70 of cell A2 and 70 of cell B2 as text. To convert text to number i use this function
4. IF - If the two characters from right side in cell A2 + 20 is equal to 2 characters from left side of cell B2, then mark as "1", else mark as "0"
• WOW thank you so much! It works great! BUT, I forgot to mention something: if my data isn't 70-1 but 070-1 , because it doesn't work in this situation :( Thanks again <3
– Etan
Apr 19 '13 at 12:32