Forum Discussion
Help needed with excel formula
In fact it's not very tricky.
COMPARE($A2;'Table 1'!$A$1:$A$4;0) search in column A in Table 1 for the position of the actual M-number in $A2 and returns the row number (lets call it r).
Then I use this row number r to search for the actual P-number (cell B$1) in row r in Table 1.
I search from column A to Z in row r in Table 1, but search from column B would have been ok as well as, as the P-numbers starts in column B and not A.
If the P-number do exist the COMPARE returns the column number - which I don't use - I just want to know that it exist. If the actual P-number don't exist in this row (r) the COMPARE will give an error and that's why I have inserted a IF.ERR around the hole statement.
And then finally I show a X if the P-number exist and an empty text if it doesn't.
Hope that make sense.
I wish you would have understood my comment and not solved the asker's homework for them.
This encourages people to cheat and ask for solutions instead of working on the solution themselves.
It would have been a much better learning experience for the asker if you had given them the chance to post their own approach and then teach based on that. You have given him a fish that he has not earned and he still does not know how to fish.