I am comparing a 100 row column to a 15000 row column on another sheet and
want to return all matches.

Ex:

A B C G H
112 3 $A$1 112 2
112 5 $A$2 112 3
118 6 $A$3 115 5
118 2
118 3
118 6

On (column I1) I am going to use:
=IF(AND($A$1:$A$20=$G1, $B$1:$B$20=$H1), SMALL($C$1:$C$20, ROW(1:1)), 0)

But my problem is that the ROW(1:1) increments to ROW(6:6), which contains
nothing and $G6 and $H6 match $A3 and $B3 respectively...how do I return
ROW(3:3) if that is where the match occurs?

Thanks.

If you are trying to identify matches in A1:B20 for each G1&H1 combination use

in I1
SUMPRODUCT(--($A$1:$A$20=G2),--($B$1:$B$20=H2))
which will return the number of matches..

OR
=IF(SUMPRODUCT(--($A$1:$A$20=G2),--($B$1:$B$20=H2))0,"Match found","")

If this post helps click Yes
---------------
Jacob Skaria


"OperationsNETTC15" wrote:

I am comparing a 100 row column to a 15000 row column on another sheet and
want to return all matches.

Ex:

A B C G H
112 3 $A$1 112 2
112 5 $A$2 112 3
118 6 $A$3 115 5
118 2
118 3
118 6

On (column I1) I am going to use:
=IF(AND($A$1:$A$20=$G1, $B$1:$B$20=$H1), SMALL($C$1:$C$20, ROW(1:1)), 0)

But my problem is that the ROW(1:1) increments to ROW(6:6), which contains
nothing and $G6 and $H6 match $A3 and $B3 respectively...how do I return
ROW(3:3) if that is where the match occurs?

Thanks.

Thanks alot!

That worked better than the 15 formulas I had tried before....however, is
there a way to return the column and cell number instead of just a 1 or a
"Match Found"?

If instead I'd get back $A$3 or something to that effect, I could
concatenate it into another formula and I'd be done with this nightmare
project.



"Jacob Skaria" wrote:

If you are trying to identify matches in A1:B20 for each G1&H1 combination use

in I1
SUMPRODUCT(--($A$1:$A$20=G2),--($B$1:$B$20=H2))
which will return the number of matches..

OR
=IF(SUMPRODUCT(--($A$1:$A$20=G2),--($B$1:$B$20=H2))0,"Match found","")

If this post helps click Yes
---------------
Jacob Skaria


"OperationsNETTC15" wrote:

I am comparing a 100 row column to a 15000 row column on another sheet and
want to return all matches.

Ex:

A B C G H
112 3 $A$1 112 2
112 5 $A$2 112 3
118 6 $A$3 115 5
118 2
118 3
118 6

On (column I1) I am going to use:
=IF(AND($A$1:$A$20=$G1, $B$1:$B$20=$H1), SMALL($C$1:$C$20, ROW(1:1)), 0)

But my problem is that the ROW(1:1) increments to ROW(6:6), which contains
nothing and $G6 and $H6 match $A3 and $B3 respectively...how do I return
ROW(3:3) if that is where the match occurs?

Thanks.

I achieved a workaround by separating my criteria by worksheet instead of
formula. Instead of IF(VALUE = 1 thru 9, do this), I took VALUES 1-9 and
gave them each a worksheet of their own, then matched them separately.

"OperationsNETTC15" wrote:

Thanks alot!

That worked better than the 15 formulas I had tried before....however, is
there a way to return the column and cell number instead of just a 1 or a
"Match Found"?

If instead I'd get back $A$3 or something to that effect, I could
concatenate it into another formula and I'd be done with this nightmare
project.



"Jacob Skaria" wrote:

If you are trying to identify matches in A1:B20 for each G1&H1 combination use

in I1
SUMPRODUCT(--($A$1:$A$20=G2),--($B$1:$B$20=H2))
which will return the number of matches..

OR
=IF(SUMPRODUCT(--($A$1:$A$20=G2),--($B$1:$B$20=H2))0,"Match found","")

If this post helps click Yes
---------------
Jacob Skaria


"OperationsNETTC15" wrote:

I am comparing a 100 row column to a 15000 row column on another sheet and
want to return all matches.

Ex:

A B C G H
112 3 $A$1 112 2
112 5 $A$2 112 3
118 6 $A$3 115 5
118 2
118 3
118 6

On (column I1) I am going to use:
=IF(AND($A$1:$A$20=$G1, $B$1:$B$20=$H1), SMALL($C$1:$C$20, ROW(1:1)), 0)

But my problem is that the ROW(1:1) increments to ROW(6:6), which contains
nothing and $G6 and $H6 match $A3 and $B3 respectively...how do I return
ROW(3:3) if that is where the match occurs?

Thanks.