Vlookup question

[mpenkala]

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

 

[preyno8269]

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")

 

[mpenkala]

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

 

[willwonka]

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.

 

[mpenkala]

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