Many Matches

[Andrea]

I am trying to make a match formula that matches an exact
number in column C with another number in column G. the
match formula matches my number in column C with the
first number in column G. I have:

=MATCH($C11,$G$9:$G$1026,0)

I was wondering if it was possible to form an exact match
with more than one number. That is, I have two numbers
(or more) in column G that are exactly the same, and I
want both of them to match with the number in column C.
Moreover, I want a formula which will find ALL the matches
at once, instead of having to make several formulas to
find the second, third, fourth match etc. Someone
suggested that i use the small formula like this, to find
the second match:

=SMALL(IF(G1:G100=C1,ROW(G1:G100)),2)

However, this did not work with what i'm trying to do.
First of all, because it only finds a number that is
smaller than the one I have (I need a function that can
find the exact match more than once), and second of all
because it does not find ALL the matches, just the
second. Is that possible for excel to do what I want? Do
you know how I can do that? I don't mind if it's messy, I
just really need to solve this!!!
Thank you for your time,
Andrea

 

[Peo Sjoblom]

I am trying to make a match formula that matches an exact
number in column C with another number in column G. the
match formula matches my number in column C with the
first number in column G. I have:

=MATCH($C11,$G$9:$G$1026,0)

I was wondering if it was possible to form an exact match
with more than one number. That is, I have two numbers
(or more) in column G that are exactly the same, and I
want both of them to match with the number in column C.
Moreover, I want a formula which will find ALL the matches
at once, instead of having to make several formulas to
find the second, third, fourth match etc. Someone
suggested that i use the small formula like this, to find
the second match:

=SMALL(IF(G1:G100=C1,ROW(G1:G100)),2)

However, this did not work with what i'm trying to do.
First of all, because it only finds a number that is
smaller than the one I have (I need a function that can
find the exact match more than once), and second of all
because it does not find ALL the matches, just the
second. Is that possible for excel to do what I want? Do
you know how I can do that? I don't mind if it's messy, I
just really need to solve this!!!
Thank you for your time,

Did you try it entered with ctrl + shift & enter?
Why are you saying that it finds a smaller number?
It will find an exact match and if you change it to

=SMALL(IF($G$1:$G$100=$C$1,ROW($G$1:$G$100)),ROW(1:1))

and copy it down until you'll get an error it will find the 1st, 2nd etc
wrap it in the index function and it can return values from cells in the
same row
from another column..

Regards,

Peo Sjoblom

 

[Jason Morin]

http://makeashorterlink.com/?V2FB23947

HTH
Jason
Atlanta, GA

 

[Alan Beban]

You might want to consider the functions in the freely downloadable file
at http://home.pacbell.net/beban:

=ArrayMatch($C11,$G$9:$G$1026), array entered into a column of at least
enough cells to accommodate the output, will return an two-column array
of the row-index and column-index numbers within the range;

and =ArrayMatch($C11,$G$9:$G$1026,"A"),similarly entered, will return a
one-column array of the worksheet addresses of the matches.

Alan Beban