• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

match multiple values in a row and return a result

L

lre712

New Member
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 1 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.

Thank you
 

Attachments

  • example_lre.xlsx
    16 KB · Views: 8
lre712 said:
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 1 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.

Thank you
Click to expand...
Hello,

You can try this below formula in Col L, If paid you got "Paid" if not "Not Paid".

=IF(INDEX(E3:E17,MATCH(1,INDEX((H3=$A$3:$A$17)*(J3=$C$3:$C$17)*(K3=$D$3:$D$17),0,1),0))="Y","Paid","Not Paid")
 
A tweak to Thangavel's solution to make it just so:
=IF(INDEX($E$3:$E$17,MATCH(1,INDEX((H3=$A$3:$A$17)*(J3=$C$3:$C$17)*(K3=$D$3:$D$17),0,1),0))="Y","Paid","Not Paid")

and an alternative:
=IF(INDEX($E$3:$E$17,MATCH(1,INDEX((H3=$A$3:$A$17)*(J3=$C$3:$C$17)*(K3=$D$3:$D$17),0,1),0))="Y","Paid","Not Paid")
which ignores the Paid column (E) since you said you only wanted to:
lre712 said:
match the value of "desciption", "PO number" and "price" from table two against Table one
Click to expand...
.
 
p45cal said:
A tweak to Thangavel's solution to make it just so:
=IF(INDEX($E$3:$E$17,MATCH(1,INDEX((H3=$A$3:$A$17)*(J3=$C$3:$C$17)*(K3=$D$3:$D$17),0,1),0))="Y","Paid","Not Paid")

and an alternative:
=IF(INDEX($E$3:$E$17,MATCH(1,INDEX((H3=$A$3:$A$17)*(J3=$C$3:$C$17)*(K3=$D$3:$D$17),0,1),0))="Y","Paid","Not Paid")
which ignores the Paid column (E) since you said you only wanted to:.
Click to expand...


Thanks P45cal, I will give both a try. I need to load more data to table 1 tomorrow and test it out. Appreciate the help.
 
You must log in or register to reply here.
Back
Top