4

First: I am trying to match a combination of data from columns D:E with a combination of data from columns A:B (Example ss per the screenshot: if the combination of data from columns D+E[Row 2] matches any from columns A:B[Rows], in this scenario D:E Row 4 is matching A:B Row 2). I would like it to displays "Paid" column F if there is a match.

Second: Column C has the respective USD amounts. What is tricky is that sometimes we have the same combo twice in columns A and B, one with a positive and one with a negative value (rows 8 and 11). In this case I would need to put "Recouped" next to the respective D:E Row data combo in column F. Please also note that the combination of data in columns D:E cannot be seen twice.

Summary: I need to find out if the combination of data in any row in columns D:E is present in columns A:B and if it is paid or recouped.

enter image description here

Improve this question
4
  • Have you consider switching to database? Commented Sep 25, 2025 at 9:14
  • 3
    Can it occur that fhshad has 2 open invoices for the amount of 244 and pays it once? Commented Sep 25, 2025 at 17:11
  • Yes, fhshad can appear more times, for example with the amount of 244 twice, and also -244. Commented Sep 26, 2025 at 10:32
  • Please edit your question to provide an example which includes the various >2 entries and what you would expect for results. Also, please explain why wwerpp|1112451 is not marked Paid Commented Sep 26, 2025 at 10:38

2 Answers 2

Reset to default
4

Here's one way to do it:

=IFERROR(IF(XLOOKUP((D.:.D&E.:.E),(A.:.A&B.:.B),C.:.C,"",,1)+XLOOKUP((D.:.D&E.:.E),(A.:.A&B.:.B),C.:.C,"",,-1)<>0,"PAID","RECOUPED"),"")

I'm assuming a record can appear at most twice in column A/B.

Results

How it works: First search for the combination using XLOOKUPS, if found return the value in column C, both from top to bottom and bottom to top; The values are summed up, if the sum is zero then you get "recouped", if it's not zero you'll get "paid". If a combination isn't found, this will trigger an error, so no value is given (and thus no sum is made). The error is resolved using IFERROR, and returns a blank string ("").

Improve this answer
1
  • Thanks a lot man. What needs to be changed if a record appears more than twice in columna A/B? Let's say 4 times, each with a different amount in Column C. Commented Sep 26, 2025 at 10:23
2

Here's another possible way to do it.

=LET(
c,LAMBDA(X,BYROW(X,CONCAT)),
R,BYROW(c(D1:E8),LAMBDA(r,SUM(--(r=c(A1:B11)))))+1,
CHOOSE(R,"","Paid","Recouped")
)
  • c is a function that concatenated the values on each row of a range, so that you have a vector. e.g. {"sddasd", 112155} becomes "sddasd112155"
  • R iterates over the rows resulting from applying function c to columns D and E and, for each row, compares the concatenated value to the result of applying c to columns A and B, converting the boolean vector to numbers with the unary operator -- then summing the result. The net result is that rows found once return 1, twice return 2 and not found return 0. Adding one to this gives us 1 for "Not found" (or ""), 2 for "Paid" and 3 for "Recouped".
  • The result of the LET is then just passing R to the index argument of CHOOSE.

enter image description here

Improve this answer
0

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.