vlookup with multiple columns

[mpenkala]

Hi there,
looking for a little help with the following:

I have 18 Groups listed in Column G
In Columns H to S I have numbers rangings from 0-40.

Each group corresponds to 12 numbers(ex.Group
A-0,3,4,5,7,11,33,34,35,36,37,40)
Some groups have some of the same numbers, but not all numbers match.

What I would like is for Column C to lookup the group in B2 and compare it
to the group in B3 and if any of the numbers within the 2 groups match, give
me "MATCH" in C3. If no match, then "MISS" in C3.

A quick example:
B2 contains Group F
B3 contains Group C
I would like C3 to compare the numbers in Group F and Group C and tell me if
any of the 12 numbers in each group are the same. If they are, I want a
"MATCH" in C3.

Thanks a bunch!
Matt

 

[T. Valko]

Try this:

Groups = range that holds the group names
Table = range of cells that hold the numbers

=IF(SUMPRODUCT(COUNTIF(INDEX(table,MATCH(B2,groups,0),),INDEX(table,MATCH(B3,groups,0),))),"Match","Miss")

 

[mpenkala]

Hi there,
thanks for your help but I'm getting a #NAME? in C3 instead if Match or
Miss... any ideas?
This is what I've inputed into C3:

=IF(SUMPRODUCT(COUNTIF(INDEX(Table,MATCH(B2,Groups,0),),INDEX(Table,MATCH(B3,Groups,0),))),"Match","Miss")

"Groups" is written in cell F1 and listed below are all the Groups.
"Table" is written in cell G1-R1 (merged cells... could this be the problem?)
G2:R38 contains the numbers associated with the groups.

Thanks.
Matt

 

[mpenkala]

Hi again,

Nevermind - I've replaced "Groups" and "Tables" with the actual range
(F2:F38) and it's working fine.... I'm a dummy sometimes.

Thanks for the help!
Matt

 

[T. Valko]

You're welcome. Thanks for the feedback!