Hi Mr Frank
I neede a further help with the same sheet only this time added three tables Friends, Enemy and Neutral and the shading
Red, Green or Neutral and if no compatibility no shading
http://www.mediafire.com/file/axpcq69n8cju9if/numero_robot.xlsx/file
thank you in anticipation
Kind regards
raj
Once again you made some errors in your cell references in your spreadsheet instructions, referring to the wrong columns in the tables. Please proof read your instructions.
Your NEUTRAL table is a mess, confusing, with duplicates of the same information, with rows missing for some other numbers, and your reference numbers in the first column of that table were all jumbled up. Excel cannot work with that !
I have rationalised that table to show only relevant non duplicated information.
In order to work out whether your 2 numbers were friends, enemies or Neutral in each case, I created a truth table K3:M10 which asks the questions and if the answer is YES, has a 1, if the answer is NO has a 0 in the table.
Note that there should only ever be a single 1 on each row, since you can't be BOTH enemies AND neutral at the same time etc. If they ever are, then your tables are to blame.
The formula to ask the question "are they friends" ? uses the MATCH function to check whether the value in col H matches any value on the row (N down in the table) where N is the number in column D. If it finds a match it returns a 1 to the truth table.
Same logic for the Enemies and Neutral questions.
Conditional formatting applied to the cells in column H, asks the question " is there a 1 in the truth table friends column ? If so colour light green.
It also asks " is there a 1 in the truth table enemies column ?" If so colour red .
It also asks " is there a 1 in the truth table neutral column ?" If so colour orange .
The normal cell fill colour is clear - for not compatible as anything.
It seems to work for the number combinations you supplied, not tested for all possibilities. The danger is it deciding it is neutral AND and enemy or friend at the same time. If you spot 2 1's on the same row of the truth table, something is not right.
Check it out and see if it works OK.
http://www.mediafire.com/file/2f7gcg0c54h2y91/numero_robot.xlsx/file