Log In
Register
Home
Active Member
March 13, 2021
Offline
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.
See Answer
VIP
Trusted Members
June 25, 2016
Offline
Hi Paula
Please attach a sample file of your data and show us what is the expected results.
Active Member
March 13, 2021
Offline
Here the sample spreadsheet. Thank you.
Trusted Members
December 20, 2019
Offline
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
Answers Post
VIP
Trusted Members
June 25, 2016
Offline
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)
1 Guest(s)
