Return Rows That Match Between Two Tables??

[Barklie]

Hi all,

---

Is there a way to cross reference two tables and return only the rows that match? In the following example I would like a formula that I could put in Columns G and H to give me the provided output.

---

[TABLE="width: 512"]
<tbody>[TR]
[TD="class: xl65, width: 128, bgcolor: transparent, colspan: 2"]Table
1
[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 128, bgcolor: transparent, colspan: 2"]Table 2
[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 128, bgcolor: transparent, colspan: 2"]MATCHES
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A
[/TD]
[TD="bgcolor: transparent, align: right"]5
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]H
[/TD]
[TD="bgcolor: transparent, align: right"]6
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]A
[/TD]
[TD="bgcolor: transparent, align: right"]5
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]B
[/TD]
[TD="bgcolor: transparent, align: right"]3
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Z
[/TD]
[TD="bgcolor: transparent, align: right"]2
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]D
[/TD]
[TD="bgcolor: transparent, align: right"]7
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]C
[/TD]
[TD="bgcolor: transparent, align: right"]8
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]A
[/TD]
[TD="bgcolor: transparent, align: right"]5
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]E
[/TD]
[TD="bgcolor: transparent, align: right"]4
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]D
[/TD]
[TD="bgcolor: transparent, align: right"]7
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Y
[/TD]
[TD="bgcolor: transparent, align: right"]5
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]H
[/TD]
[TD="bgcolor: transparent, align: right"]6
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]E
[/TD]
[TD="bgcolor: transparent, align: right"]4
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]R
[/TD]
[TD="bgcolor: transparent, align: right"]7
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]F
[/TD]
[TD="bgcolor: transparent, align: right"]9
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]T
[/TD]
[TD="bgcolor: transparent, align: right"]7
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]G
[/TD]
[TD="bgcolor: transparent, align: right"]2
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]L
[/TD]
[TD="bgcolor: transparent, align: right"]3
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]H
[/TD]
[TD="bgcolor: transparent, align: right"]6
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]E
[/TD]
[TD="bgcolor: transparent, align: right"]4
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]D
[/TD]
[TD="bgcolor: transparent, align: right"]7
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Q
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

 

[venkat1926]

Sheet1

*

A

B

C

D

E

F

G

H

I

J

K

L

Table 1

*

*

*

Table 2

*

*

*

MATCHES

*

*

formla

*

*

*

*

*

*

*

*

*

*

*

*

A

A,5

*

A

A,5

*

A

*

A,5

B

B,3

*

E

E,4

*

D

*

E,4

C

C,8

*

H

H,6

*

E

*

H,6

D

D,7

*

L

L,3

*

H

*

*

D

D,7

*

R

R,7

*

*

*

*

*

E

E,4

*

T

T,7

*

*

*

*

*

F

F,9

*

Y

Y,5

*

*

*

*

*

G

G,2

*

Z

Z,2

*

*

*

*

*

H

H,6

*

*

*

*

*

*

*

*

*

Q

Q,1

*

*

*

*

*

*

*

*

*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: right"]5[/TD]

[TD="align: right"]5[/TD]

[TD="align: right"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"]3[/TD]

[TD="align: right"]4[/TD]

[TD="align: right"]7[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="align: right"]8[/TD]

[TD="align: right"]6[/TD]

[TD="align: right"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="align: right"]7[/TD]

[TD="align: right"]3[/TD]

[TD="align: right"]6[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="align: right"]7[/TD]

[TD="align: right"]7[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="align: right"]4[/TD]

[TD="align: right"]7[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="align: right"]9[/TD]

[TD="align: right"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

[TD="align: right"]2[/TD]

[TD="align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]

[TD="align: right"]6[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]

[TD="align: right"]1[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula C3 =A3&","&B3 L3 =IF(COUNTIF(C3:C12,G3)>0,G3,"") <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4


see formula in C3 which is copied down c4 to c12 and also G3 and down.
the formula in L3 is copied down up to L12

now manipulate cells containing data in column L into text to columns with delimiter as comma(,)

D7 is not given because it is duplicate in the same table

if you do not like so many operations you can have a macro

 

[venkat1926]

mistake in the formula for L3 it should be

=IF(COUNTIF($C$3:$C$12,G3)>0,G3,"")

 

[venkat1926]

ok taking into account duplicates either in the same table or the two tables try this formula in M3

=IF(OR(COUNTIF(C3:$C$12,C3)>=2,COUNTIF($C$3:$C$12,G3)>=1),C3,"")

copy down

 

[Barklie]

Hi Venkat, I couldn't get that formula to work because my rows don't match up the same that yours do in the example. I have a more comprehensive description of my problem at: http://www.mrexcel.com/forum/excel-...ernate-vlookup-expert-needed.html#post3872343. I appreciate your response though!