Conditional formatting with INDEX MATCH MATCH

Hi, hoping someone can help me with what seems like a fairly complex formula.

I am trying to check whether given values are 'allowed' according to a list of predefined values, using conditional formatting.

My first issue was to check whether items in one column appear in another column. This was fairly straightforward and I used:
=ISERROR(MATCH(G2,$J$2:$J$5,0))
where G2 is the value to check, and J2:J5 is the list of predefined values. This is working fine.

Now what I want to do, is check value H2 in a multi-column/row table. The row it should check in can be found with value G2, but it could appear in any column of the table.

So far, I've got:
=ISERROR(INDEX(K2:U5,MATCH(G2,J2:J5,0),MATCH(H2,K2:U5,0)))

Where K2:U5 is the table that H2 could appear in
J2:J5 is the column that G2 could appear in. But this just gives me an error.

In plan English I want it to look at value G2, find out what row that's in in column J (say row x), and then look to see whether value H2 appears in any of the columns of row x in the specified table.
The image below shows where it should be highlighted

Capture.PNG

Very confused, really hope someone can help me!

Thanks,
Jess

How about this for conditional formatting formula in H2 and down?

Formula:

Please Login or Register  to view this content.

See attached.

BSB

I think it should be

=SUMPRODUCT(($J$2:$J$5=G6)*($K$2:$U$5=H6)) (TRUE)

??

Originally Posted by JohnTopley

I think it should be

=SUMPRODUCT(($J$2:$J$5=G6)*($K$2:$U$5=H6)) (TRUE)

??

Surely that would highlight the valid selections in column H. The image in post#1 shows the invalid ones to be highlighted.

BSB

I cannot see the image so ASSUMED from description ...

and then look to see whether value H2 appears in any of the columns of row x in the specified table.

... that match is highlighted. Bot of course if reverse is true, your formula is correct.

My apologies!!

Originally Posted by JohnTopley

I cannot see the image so ASSUMED from description ...

PHEW!
Had me doubting myself for a moment there, Mr Topley!

BSB

Hi,
Thanks for all your suggestions so far.
I think unfortunately none of them are working, because they're not taking into account that a pack size might be incorrect for one product, but correct for another.

BSB, I can see that your formula works in the downloaded document, but for some reason it's having no effect in my version...

My error - it actually works completely perfectly and is so much simpler than the way I was trying to do it!
Thank you so much for your help

Happy to help.
Thanks for the rep point

BSB

Hi again BSB,

You wouldn't be able to tell me how to convert my original formula to work more like yours would you? I think yours is better and it also seems more flexible!
So something like =SUMPRODUCT($J$2:$J$5=G2)=0 (which doesn't work) to indicate instances where G2 cannot be found in J2:J5.

I've been reading about SUMPRODUCT to try and do this myself, and whilst I think I understand the principles (it applies a 1/0 - TRUE/FALSE value to each instance), I can't understand how it could be used with just one array!

Thanks,
Jess

Try

=SUMPRODUCT(--($J$2:$J$5=G2))=0

Then read into SUMPRODUCT with a double unary "--" to understand it.

Or use your formula with *1 at the end.

Note: Not tested as I'm replying from my phone so don't have Excel open.

BSB