Return value if two criteria match

**patstickets** · 08-09-2012, 07:31 AM

At the moment I have a sheet of items that I have sold (sold sheet) and then another sheet which shows which items have currently been paid (paid sheet), What I would like is to insert a column into the Sold sheet called "PAID" which would automatically lookup in the paid sheet to see if that item has yet been paid for, and if the full amount has been paid then it would return "YES" and if not the full amount has been paid then it would show the minus value of what is missing e.g. "£-5" in the case of ID 102 in the example below.

Sold Sheet

ID PRICE PAID
100 99
101 150
102 175

Paid Sheet

ID PRICE
100 99
101 150
102 170

In my spreadsheet the ID column is column 'i' and PRICE is column 'K', and I would like the PAID result to show in column 'R'. any help would be greatly appreciated. Thanks

**vlady** · 08-09-2012, 08:00 AM

hello
change the references as needed.

indexmatch.xlsx

=IF(INDEX(Sheet2!$B$2:$B$4,MATCH(A4,Sheet2!$A$2:$A$4,0))=B4,"yes",INDEX(Sheet2!$B$2:$B$4,MATCH(A4,Sheet2!$A$2:$A$4,0))-B4)

misplaced -B4

**NBVC** · 08-09-2012, 08:03 AM

Something like:

=IF(SUMIF(PAID!I:I,A2,PAID!K:K)-B2,SUMIF(PAID!I:I,A2,PAID!K:K)-B2,"Yes")

adjust sheetname and references as necessary (A2 contains first ID in Sold Sheet, and B2 contains Price sold at)