| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup with multiple columns
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
vlookup with multiple columns
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") -- Biff Microsoft Excel MVP "mpenkala" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
vlookup with multiple columns
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 "T. Valko" wrote: 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") -- Biff Microsoft Excel MVP "mpenkala" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
vlookup with multiple columns
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 "mpenkala" wrote: 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 "T. Valko" wrote: 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") -- Biff Microsoft Excel MVP "mpenkala" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
vlookup with multiple columns
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "mpenkala" wrote in message ... 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 "mpenkala" wrote: 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 "T. Valko" wrote: 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") -- Biff Microsoft Excel MVP "mpenkala" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| VLOOKUP, IF and multiple columns | Excel Discussion (Misc queries) | |||
| vlookup for multiple columns | Excel Worksheet Functions | |||
| Multiple Columns & VLookup?? | Excel Worksheet Functions | |||
| VLOOKUP and multiple columns | Excel Discussion (Misc queries) | |||
| vlookup from multiple columns | Excel Worksheet Functions | |||
Linear Mode
