I need to see if any of the item numbers in column X, match any of the highlighted blue numbers in columns M, O or Q. if so, I need them identified some kind of way. I believe this is an IF function that may also use Conditional Formatting, but not quite sure how the function would go. Looks like my files size exceeds the limit, but if you can help with the formula I would love it.
Hi Paula
Please attach a sample file of your data and show us what is the expected results.
Here the sample spreadsheet. Thank you.
https://drive.google.com/file/d/1NGo6I2Q9htWldmbSA97GJEMiGf3oPP1U/view?usp=sharing
All the formulas are broken on the attachment when i open it, but if you just wanto to see if any item in Column x is in the specific cells i would use match.
However a lot depends on if you want it next to the row on the with the blue cells highlighted or next to the item number in column x - i have done it both ways on the attached - the number means the row that the number has been found on
Hi Paula
You should use MATCH as mentioned by Purfleet in your Conditional Formatting.
Example =MATCH(M14,$X:$X,0) and applied to cell M14
You can then use the Format Painter (the brush on the Ribbon) to apply to O4, Q4 etc.
One thing you need to take note of is that your column X is formatted as TEXT.
If the data in column M,O or Q is formatted as number, the above formula will fail.
It is not advisable to format any cell as TEXT as it can cause a lot of problem.
Any cell with a formula that refers to another cell formatted as TEXT will automatically follow the formatting (i.e. TEXT).
If you later edit that formula, the formula will be converted to text. It will no longer be a formula.
Sunny
(Excel 2019)
Thank you everyone.
