Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup question
Hi there,
I have another question/problem which involves vlookup. It's similar to my last one, but with a little twist. I have 18 Groups listed in Column F In Columns G to R 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 C3 to lookup the number in A3 (Column A lists a single number between 0-40) and compare it to the numbers associated with the group listed in in B2. If the number in A3 is found in the group of numbers associated with the group in B2 then "Match" is given in C3, if not "Miss". A quick example: B2 contains Group F (1,2,4,5,7,10,11,17,24,26,30,38) A3 = 26 I would like C3 to compare the number in A3 (26) to Group F (cell B2) and tell me if the number (26) is present in the group (it is in this case). If it is, I want a "MATCH" in C3. Thanks a bunch! Matt |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup question
So just to make sure I understand the question.
In A3 is the number 26 in B2 is the text "Group F" in C3 you want a formula to test if 26 is in Group F which is identified in columns F through R where column F is the Group I have assigned a range name to Column F called "groups". (not the whole column just the rows with groups). Also no headers on the range name. Here is a formula. Maybe not the best; but it should work. =IF(COUNTIF(OFFSET(F4,MATCH(B3,groups,0)-1,0,1,14),A4)0,"Match","no Match") On Dec 12, 8:15 am, mpenkala wrote: Hi there, I have another question/problem which involves vlookup. It's similar to my last one, but with a little twist. I have 18 Groups listed in Column F In Columns G to R 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 C3 to lookup the number in A3 (Column A lists a single number between 0-40) and compare it to the numbers associated with the group listed in in B2. If the number in A3 is found in the group of numbers associated with the group in B2 then "Match" is given in C3, if not "Miss". A quick example: B2 contains Group F (1,2,4,5,7,10,11,17,24,26,30,38) A3 = 26 I would like C3 to compare the number in A3 (26) to Group F (cell B2) and tell me if the number (26) is present in the group (it is in this case). If it is, I want a "MATCH" in C3. Thanks a bunch! Matt |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup question
Hi Preyno,
yes this works good, thank you. But just to make this more complicated, I would like to add the following (if possible): As it stands, if the number in col.A isn't part of the previous group, Col.C records a "miss". Now what I want to do is if it's a "miss" continue comparing the next number in col.A to that SAME GROUP, not the previous group. Example Col.A Col.B Col.C 4 Group2 7 Group4 Miss (Here we check to see if 7 is part of Group2, it isn't) 21 Group1 Miss (because we had a Miss above, we check to see if 21 is part of Group 2, it isn't) 33 Group9 Match (again because of the miss above, we check to see if 33 is part of Group 2. It is so we get a Match) After a Match, we would start over again, comparing the number to the group previous. Thanks for your help - good luck. Mat " wrote: So just to make sure I understand the question. In A3 is the number 26 in B2 is the text "Group F" in C3 you want a formula to test if 26 is in Group F which is identified in columns F through R where column F is the Group I have assigned a range name to Column F called "groups". (not the whole column just the rows with groups). Also no headers on the range name. Here is a formula. Maybe not the best; but it should work. =IF(COUNTIF(OFFSET(F4,MATCH(B3,groups,0)-1,0,1,14),A4)0,"Match","no Match") On Dec 12, 8:15 am, mpenkala wrote: Hi there, I have another question/problem which involves vlookup. It's similar to my last one, but with a little twist. I have 18 Groups listed in Column F In Columns G to R 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 C3 to lookup the number in A3 (Column A lists a single number between 0-40) and compare it to the numbers associated with the group listed in in B2. If the number in A3 is found in the group of numbers associated with the group in B2 then "Match" is given in C3, if not "Miss". A quick example: B2 contains Group F (1,2,4,5,7,10,11,17,24,26,30,38) A3 = 26 I would like C3 to compare the number in A3 (26) to Group F (cell B2) and tell me if the number (26) is present in the group (it is in this case). If it is, I want a "MATCH" in C3. Thanks a bunch! Matt |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup question
The only thing that I can think of is possibly adding an additional
column to keep a running tab of the Group you want to compare in case of a miss. For example, in Column D put a formula =If (c3="Miss",d2,b3) This will keep track of the group you want to compare. Then formula in Column C might look something like: =IF(COUNTIF(OFFSET($F$4,MATCH(d3,groups, 0)-1,0,1,14),A4)0,"Match","Miss") I'm not sure if I have all of the components; but hopefully this helps. On Dec 12, 10:16 am, mpenkala wrote: Hi Preyno, yes this works good, thank you. But just to make this more complicated, I would like to add the following (if possible): As it stands, if the number in col.A isn't part of the previous group, Col.C records a "miss". Now what I want to do is if it's a "miss" continue comparing the next number in col.A to that SAME GROUP, not the previous group. Example Col.A Col.B Col.C 4 Group2 7 Group4 Miss (Here we check to see if 7 is part of Group2, it isn't) 21 Group1 Miss (because we had a Miss above, we check to see if 21 is part of Group 2, it isn't) 33 Group9 Match (again because of the miss above, we check to see if 33 is part of Group 2. It is so we get a Match) After a Match, we would start over again, comparing the number to the group previous. Thanks for your help - good luck. Mat " wrote: So just to make sure I understand the question. In A3 is the number 26 in B2 is the text "Group F" in C3 you want a formula to test if 26 is in Group F which is identified in columns F through R where column F is the Group I have assigned a range name to Column F called "groups". (not the whole column just the rows with groups). Also no headers on the range name. Here is a formula. Maybe not the best; but it should work. =IF(COUNTIF(OFFSET(F4,MATCH(B3,groups,0)-1,0,1,14),A4)0,"Match","no Match") On Dec 12, 8:15 am, mpenkala wrote: Hi there, I have another question/problem which involves vlookup. It's similar to my last one, but with a little twist. I have 18 Groups listed in Column F In Columns G to R 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 C3 to lookup the number in A3 (Column A lists a single number between 0-40) and compare it to the numbers associated with the group listed in in B2. If the number in A3 is found in the group of numbers associated with the group in B2 then "Match" is given in C3, if not "Miss". A quick example: B2 contains Group F (1,2,4,5,7,10,11,17,24,26,30,38) A3 = 26 I would like C3 to compare the number in A3 (26) to Group F (cell B2) and tell me if the number (26) is present in the group (it is in this case). If it is, I want a "MATCH" in C3. Thanks a bunch! Matt- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup question
Hey,
yes this works. This is actually what I ended up using. Was hoping there was an easier way, but atleast it works. I had to use about 6 extra columns, but once I group and hide them, the sheet looks impressive. Cheers, Matt "willwonka" wrote: The only thing that I can think of is possibly adding an additional column to keep a running tab of the Group you want to compare in case of a miss. For example, in Column D put a formula =If (c3="Miss",d2,b3) This will keep track of the group you want to compare. Then formula in Column C might look something like: =IF(COUNTIF(OFFSET($F$4,MATCH(d3,groups, 0)-1,0,1,14),A4)0,"Match","Miss") I'm not sure if I have all of the components; but hopefully this helps. On Dec 12, 10:16 am, mpenkala wrote: Hi Preyno, yes this works good, thank you. But just to make this more complicated, I would like to add the following (if possible): As it stands, if the number in col.A isn't part of the previous group, Col.C records a "miss". Now what I want to do is if it's a "miss" continue comparing the next number in col.A to that SAME GROUP, not the previous group. Example Col.A Col.B Col.C 4 Group2 7 Group4 Miss (Here we check to see if 7 is part of Group2, it isn't) 21 Group1 Miss (because we had a Miss above, we check to see if 21 is part of Group 2, it isn't) 33 Group9 Match (again because of the miss above, we check to see if 33 is part of Group 2. It is so we get a Match) After a Match, we would start over again, comparing the number to the group previous. Thanks for your help - good luck. Mat " wrote: So just to make sure I understand the question. In A3 is the number 26 in B2 is the text "Group F" in C3 you want a formula to test if 26 is in Group F which is identified in columns F through R where column F is the Group I have assigned a range name to Column F called "groups". (not the whole column just the rows with groups). Also no headers on the range name. Here is a formula. Maybe not the best; but it should work. =IF(COUNTIF(OFFSET(F4,MATCH(B3,groups,0)-1,0,1,14),A4)0,"Match","no Match") On Dec 12, 8:15 am, mpenkala wrote: Hi there, I have another question/problem which involves vlookup. It's similar to my last one, but with a little twist. I have 18 Groups listed in Column F In Columns G to R 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 C3 to lookup the number in A3 (Column A lists a single number between 0-40) and compare it to the numbers associated with the group listed in in B2. If the number in A3 is found in the group of numbers associated with the group in B2 then "Match" is given in C3, if not "Miss". A quick example: B2 contains Group F (1,2,4,5,7,10,11,17,24,26,30,38) A3 = 26 I would like C3 to compare the number in A3 (26) to Group F (cell B2) and tell me if the number (26) is present in the group (it is in this case). If it is, I want a "MATCH" in C3. Thanks a bunch! Matt- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP question | Excel Discussion (Misc queries) | |||
Vlookup Question | Excel Worksheet Functions | |||
VLOOKUP question | Excel Worksheet Functions | |||
=vlookup question | Excel Discussion (Misc queries) | |||
VLOOKUP Question | Excel Worksheet Functions |