Various Matches

  • Thread starter Thread starter Andrea
  • Start date Start date
A

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, 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?
Thank you for your time,
Andrea
 
To return several values in one cell is a pain. I would
recommend you use the second formula I gave you and fill
it down so that it updates with finding the 3rd, 4th, etc.

Try this in A1 (for the first match):

=SMALL(IF($G$9:$G$1026=$C$11,ROW($G$9:$G$1026)),ROW())

and press ctrl/shift/enter. Then fill the formula down
until you get an error value.

HTH
Jason
Atlanta, GA
 
Back
Top