Reference Number Lookup

[Bertie88!]

Hello,

I'm trying to write some VBA to pull back an invoice number based on a match of criteria in two data sources.

Source one

Invoices</SPAN>

[TABLE="width: 144"]
<COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY>[TR]
[TD="class: xl65, width: 192, bgcolor: transparent, colspan: 3"]Aug-12[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]1358[/TD]
[TD="class: xl67, bgcolor: transparent"]TEST1[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]160[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]1359[/TD]
[TD="class: xl67, bgcolor: transparent"]TEST2[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]110[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]1360[/TD]
[TD="class: xl67, bgcolor: transparent"]TEST3[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]2005[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]1361[/TD]
[TD="class: xl67, bgcolor: transparent"]TEST4[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]1770[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]1362[/TD]
[TD="class: xl67, bgcolor: transparent"]TEST5[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]2405[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]1363[/TD]
[TD="class: xl67, bgcolor: transparent"]TEST6[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]3390[/TD]
[/TR]
</TBODY>[/TABLE]


Source two

Payments

[TABLE="width: 153"]
<COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 57pt; mso-width-source: userset; mso-width-alt: 2779" width=76><TBODY>[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl66, width: 76, bgcolor: transparent"]Invoice Ref[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]TEST1[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]160[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]TEST2[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]110[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]TEST3[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2005[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]TEST4[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1770[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]TEST5[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2405[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]TEST6[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3390[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[/TR]
</TBODY>[/TABLE]


I need to get the invoice reference (column A) in source one to appear in source two (column C) if the name and amount match.

can you help?

 

[Gopalakrishnan]

Hopefully this could help you to workout!

Formula to be used: =IF(AND(D4=F4,E4=G4), C4, "Mismatch") --> drag until last row

My assumption from your req. is below:

[TABLE="width: 454"]
<tbody>[TR]
[TD][/TD]
[TD="colspan: 3, align: center"]Source 1[/TD]
[TD="colspan: 2, align: center"]Source 2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Col/Row #[/TD]
[TD] C
[/TD]
[TD] D[/TD]
[TD] E[/TD]
[TD] F
[/TD]
[TD] G
[/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]1358[/TD]
[TD]TEST1[/TD]
[TD]160[/TD]
[TD]TEST1[/TD]
[TD]160[/TD]
[TD]1358[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]1359[/TD]
[TD]TEST2[/TD]
[TD]111[/TD]
[TD]TEST2[/TD]
[TD]110[/TD]
[TD]Mismatch[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]1360[/TD]
[TD]TEST3[/TD]
[TD]2005[/TD]
[TD]TEST3[/TD]
[TD]2005[/TD]
[TD]1360[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD]1361[/TD]
[TD]TEST4[/TD]
[TD]1770[/TD]
[TD]TEST4[/TD]
[TD]1770[/TD]
[TD]1361[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD]1362[/TD]
[TD]TEST5[/TD]
[TD]2405[/TD]
[TD]TEST5[/TD]
[TD]2405[/TD]
[TD]1362[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD]1363[/TD]
[TD]TEST6[/TD]
[TD]3390[/TD]
[TD]TEST6[/TD]
[TD]3390[/TD]
[TD]1363[/TD]
[/TR]
</tbody>[/TABLE]