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):

enter image description here

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


  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


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)

enter image description here


Array Formulas: See THIS

Explaining the formula


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.


  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.