CODEWITHSUNDEEP
excelexcel-formula
user101998
user101998

Reputation: 251

Excel - returning a cell in a row only if multiple criteria from current row are matched

I have a spreadsheet of transactions with unique IDs.

For each transaction, there may be two rows corresponding---one initiating, one fulfilling.

As new information keeps flowing in, I want to find the initiating rows if the current row is a fulfilling. Since they will have different IDs, the only way to match them is by the amount, type, account number, and transaction date.

Each of the criteria are not unique. For example, there may be many transactions with amount of $500.

As a result, I want to write a formula that returns the transaction ID if and only if all these criteria match a row in the spreadsheet.

I tried to work with arrays thinking i can multiply them since they will be either 0 or 1 and the product will only be 1 if all criteria match, but could not get it to work since it will just return one value, instead of an array of values.

So the spreadsheet has the following structure:

ID | Account Number | Transaction Date | Amount | Customer | Fulfilling order ID?    |
1 |     123456      |     06/26/2018   |   50   |   John   |                         |
2 |     123457      |     07/25/2018   |   55   |   Steve  |                         |
3 |     123456      |     06/26/2019   |   50   |   John   |         1               |
4 |     124588      |     07/25/2019   |   75   |   Rich   |                         |
5 |     125589      |     08/01/2019   |   80   |   Vincent|                         |

I want to be able to write something that will return 1, on order ID 3, since it is fulfilling order ID 1.

Upvotes: 0

Views: 448

Answers (1)

JvdV
JvdV

Reputation: 75990

You can certainly use dynamic ranges within array formulas, so you don't have to manually keep changing ranges when you enter data.

Something in cell F2 along the lines of:

=INDEX(A:A,MATCH(1,($B$1:INDEX(B:B,COUNTA(B:B))=B2)*($C$1:INDEX(C:C,COUNTA(C:C))=C2)*($D$1:INDEX(D:D,COUNTA(D:D))=D2)*($E$1:INDEX(E:E,COUNTA(E:E))=E2),0))

Entered as array through CtrlShiftEnter, wouldnt work?

Note In your example it would never return a 1, but just a 3 simply because the criteria don't match (column c, dates are different). As soon as they are the same, the formula works.

enter image description here

Upvotes: 2

Related Questions