View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.misc
OperationsNETTC15 OperationsNETTC15 is offline
external usenet poster
 
Posts: 14
Default Comparing different length columns

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.