Comparing different length columns

O

OperationsNETTC15

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.

J

Jacob Skaria

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

O

OperationsNETTC15

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.

O

OperationsNETTC15

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.