conditional formatting with formula problem

[chuath]

Hi Experts,

This is a follow up from my previous thread https://www.excelforum.com/excel-gen...ml#post5776804 and I was doing some experiment with it to highlight the numbers in Number2 Column by using the formula =INDEX($D$2:$D$1000,MATCH($F2&G2,$B$2:$B$1000&$C$2:$C$1000,0))>0 in conditional formatting.

Number1 Number2 Frequency
2 60 1
2 54 2
2 34 2
2 22 1
2 32 1
2 42 1
2 40 3
2 36 1
2 48 1
2 44 1
2 28 1
2 30 2
2 24 1
2 58 1
2 26 2
2 56 1
2 52 2
2 38 1
2 46 1
2 50 1

But there's some error as boxed in red below which they shouldn't be highlighted. Pls advise if it's the equation in the conditional formatting which is wrong resulting in this happening and if yes, pls help to resolve this by providing the correct equation. Thanks.

1.png

[etaf]

because you are combining values
for example

62 and 2
that becomes
622
and 6 and 22 is then seen as true

not sure why at the moment .... as i would , like you expect the 62 to be looked up in column B not the 6
BUT the columns are not treated as separate

its just combining them with concatenate

means
62 2 is the same as 6 22

at least thats what i worked out - but copying the formula and playing around

i'll look to see if i can find a solution

[etaf]

ok, how about
=LOOKUP(2,1/($B$2:$B$1000=$F2)/($C$2:$C$1000=G2),($D$2:$D$1000))

I tried sumproduct - but could not get that to work

other members may have a better solution

seems to work OK

[chuath]

ok, how about
=LOOKUP(2,1/($B$2:$B$1000=$F2)/($C$2:$C$1000=G2),($D$2:$D$1000))

I tried sumproduct - but could not get that to work

other members may have a better solution

seems to work OK

Hi etaf,

It works. If I encounter some other issue, I'll ask u again. For the lookup value 2 in the formula, may I know must it be 2 or can be some other value? Thanks.

[etaf]

it can be any number starting at 1
I just used an old reference website i have .

i have a lot of sites i book mark - for various solutions to different problems in excel - over the years

https://www.microsoft.com/en-us/micr...okup-formulas/

If you use 2 as the lookup value, then the formula will match it with the last numeric value in the range, that is, the last row where both conditions are True. This is the “vector form” of the LOOKUP, so you can use it to get the corresponding value returned from C3:C13. I used 2 as the LOOKUP value, but it can be any number, starting at 1. If the formulas don’t find any match, you will, of course, get a #N/A error!

[chuath]

it can be any number starting at 1
I just used an old reference website i have .

i have a lot of sites i book mark - for various solutions to different problems in excel - over the years

https://www.microsoft.com/en-us/micr...okup-formulas/

Hi etaf,

Thanks. I've bookmarked the link that you have sent. Thanks again.

[etaf]

you are welcome