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

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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
353
The Godfather
T
D
  • Question Question
Print Multiple invoices in one PDF File
Replies
1
Views
345
John_w
J
R
  • Question Question
Match value where same value occurs multiple times
Replies
6
Views
189
Rijnsent
Rijnsent
B
  • Question Question
Excel Table Lookup with OR Conditions and Multiple Columns
Replies
1
Views
147
BenW71
B
L
  • Solved
Lookup and retrieve value based on table code (2)
Replies
3
Views
459
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,750
Messages
6,180,740
Members
452,996
Latest member
nelsonsix66

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