I have named ranges and trying to find out if the said value is there. It works when two ranges are matched separately, but I am not able to trace how to combine it. I have to match in a cell around 10range & 10 variable.
=IF(ISNUMBER(MATCH(2,blue,0)),,2)&IF(ISNUMBER(MATCH(2,grey,0)),,2)&IF(ISNUMBER(MATCH(3,blue,0)),,3)&IF(ISNUMBER(MATCH(3,grey,0)),,3) – result 3- works perfect as grey range has not 3.
But when I try to combine it does not work
=IF(ISNUMBER(MATCH(2,((blue*grey)),0)),,2) result 2 while range blue has 2
=IF(ISNUMBER(MATCH(2,(OR(blue,grey)),0)),,2) result 2 while range blue has 2
=IF(ISNUMBER(MATCH(2,(AND(blue,grey)),0)),,2) result 2 while range blue has 2
I have to combine around 10 range & 10 match(result in a ingle cell) so I want to shorten the formula. Also to take care that some cell may have their places in both cell. Like Red(a1,b1,c1) & blue(a1,a2,a3)
Where a1 is common in red and blue.
Thanks in advance as always i have received best answers to my querry from this forum
Bookmarks