Match multiple values in the same row to the same values in another table

L

lre172

New Member
Joined
Oct 31, 2017
Messages
2
Hi everyone

I have data in two tables. The first table contains invoices that have been paid. The second table is invoices that have been submitted for payment. I need the check the invoices in table 2 against table one to see if they have been paid already.

I want to match the value of "desciption", "PO number" and "price" from table two against Table one, and return the value "PAID" if all values on the row match.

Link to the document below.



https://www.dropbox.com/s/bep4thrg3383uvc/example_lre.xlsx?dl=0


Cheers
Lee ?
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Sort by date Sort by votes
[TABLE="width: 985"]
<colgroup><col span="12"><col><col span="2"></colgroup><tbody>[TR]
[TD]decription[/TD]
[TD]po num[/TD]
[TD]price[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]decription[/TD]
[TD]po num[/TD]
[TD]price[/TD]
[TD]status[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]descr1[/TD]
[TD]po1[/TD]
[TD="align: right"]305[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]descr1[/TD]
[TD]po1[/TD]
[TD="align: right"]305[/TD]
[TD]paid[/TD]
[TD]#####[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]descr3[/TD]
[TD]po3[/TD]
[TD="align: right"]320[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]descr10[/TD]
[TD]po 10[/TD]
[TD="align: right"]560[/TD]
[TD]outstanding[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]descr5[/TD]
[TD]po5[/TD]
[TD="align: right"]335[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]descr11[/TD]
[TD]po11[/TD]
[TD="align: right"]380[/TD]
[TD]paid[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]descr7[/TD]
[TD]po7[/TD]
[TD="align: right"]350[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]descr12[/TD]
[TD]po 12[/TD]
[TD="align: right"]575[/TD]
[TD]outstanding[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]descr9[/TD]
[TD]po9[/TD]
[TD="align: right"]365[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]descr13[/TD]
[TD]po13[/TD]
[TD="align: right"]395[/TD]
[TD]paid[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]descr11[/TD]
[TD]po11[/TD]
[TD="align: right"]380[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]descr14[/TD]
[TD]po 14[/TD]
[TD="align: right"]590[/TD]
[TD]outstanding[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]descr13[/TD]
[TD]po13[/TD]
[TD="align: right"]395[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]descr15[/TD]
[TD]po15[/TD]
[TD="align: right"]410[/TD]
[TD]paid[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]descr15[/TD]
[TD]po15[/TD]
[TD="align: right"]410[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]descr16[/TD]
[TD]po 16[/TD]
[TD="align: right"]605[/TD]
[TD]outstanding[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]descr17[/TD]
[TD]po17[/TD]
[TD="align: right"]425[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]descr17[/TD]
[TD]po17[/TD]
[TD="align: right"]425[/TD]
[TD]paid[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]descr18[/TD]
[TD]po 18[/TD]
[TD="align: right"]620[/TD]
[TD]outstanding[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]descr2[/TD]
[TD]po 2[/TD]
[TD="align: right"]500[/TD]
[TD]outstanding[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]descr3[/TD]
[TD]po3[/TD]
[TD="align: right"]320[/TD]
[TD]paid[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]descr4[/TD]
[TD]po4[/TD]
[TD="align: right"]515[/TD]
[TD]outstanding[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]descr5[/TD]
[TD]po5[/TD]
[TD="align: right"]335[/TD]
[TD]paid[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]descr6[/TD]
[TD]po 6[/TD]
[TD="align: right"]530[/TD]
[TD]outstanding[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]descr7[/TD]
[TD]po7[/TD]
[TD="align: right"]350[/TD]
[TD]paid[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]descr8[/TD]
[TD]po 8[/TD]
[TD="align: right"]545[/TD]
[TD]outstanding[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]descr9[/TD]
[TD]po9[/TD]
[TD="align: right"]365[/TD]
[TD]paid[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]#####[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 10"]=IF(SUMPRODUCT(($A$2:$A$10=J2)*($B$2:$B$10=K2)*($C$2:$C$10=L2)*1)=1,"paid","outstanding")[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question Question
A semi-advanced matching problem which may be solved with formulization or VBA
Replies
1
Views
334
The Godfather
T
D
  • Question Question
Print Multiple invoices in one PDF File
Replies
1
Views
286
John_w
J
R
  • Question Question
Match value where same value occurs multiple times
Replies
6
Views
174
Rijnsent
Rijnsent
L
  • Solved
Lookup and retrieve value based on table code (2)
Replies
3
Views
408
Peter_SSs
Peter_SSs
K
  • Question Question
Data Validation Based or using Index Match
Replies
2
Views
596
komboteam
K

Forum statistics

Threads
1,223,713
Messages
6,174,043
Members
452,542
Latest member
Bricklin

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top