Need Help with Formula for Payment Status Update in Excel

[happydz]

I am working with an Excel spreadsheet that tracks client orders and payments. I have two sets of columns: one for client names and their ordered quantities, and another for client names and their paid quantities. I need assistance with a function that accurately updates the payment status based on whether the ordered quantities match the paid quantities.I have already implemented a formula, but it incorrectly categorizes some clients as "not billed" even when their ordered and paid quantities match. I would appreciate any guidance on how to correct this issue or improve my existing formula.

 

[myall_blues]

Which version of Excel are you using? It's helpful if you can update your profile to indicate your Excel version?

 

[myall_blues]

In the meantime, using your example, try this formula in E2 and copy down:

=IF(ISNA(INDEX($A$2:$A$3,MATCH(1,(C2=$A$2:$A$3)*(D2=$B$2:$B$3),0))),"Not yet","Billed")

If you're not using Excel 2021 or 365, this needs to be enetered as an array formula using CTRL+SHIFT+ENTER.

 

[happydz]

In the meantime, using your example, try this formula in E2 and copy down:

=IF(ISNA(INDEX($A$2:$A$3,MATCH(1,(C2=$A$2:$A$3)*(D2=$B$2:$B$3),0))),"Not yet","Billed")

If you're not using Excel 2021 or 365, this needs to be enetered as an array formula using CTRL+SHIFT+ENTER.

Not working as expected, you can notice for example that the value in cell B2 is exactly the same as in the cell D3 for the same client, but when running the formula in E2, it indicates "Not yet" see the attached screenshot.
I've updated the OP with my Excel version.

 

[happydz]

I am working with an Excel spreadsheet that tracks client orders and payments. I have two sets of columns: one for client names and their ordered quantities, and another for client names and their paid quantities. I need assistance with a function that accurately updates the payment status based on whether the ordered quantities match the paid quantities.I have already implemented a formula, but it incorrectly categorizes some clients as "not billed" even when their ordered and paid quantities match. I would appreciate any guidance on how to correct this issue or improve my existing formula.

Mcrosoft Office Professionnel Plus 2010

Ver. 14.0.6023.1000 (64 bits)

 

[happydz]

In the meantime, using your example, try this formula in E2 and copy down:

=IF(ISNA(INDEX($A$2:$A$3,MATCH(1,(C2=$A$2:$A$3)*(D2=$B$2:$B$3),0))),"Not yet","Billed")

If you're not using Excel 2021 or 365, this needs to be enetered as an array formula using CTRL+SHIFT+ENTER.

Mcrosoft Office Professionnel Plus 2010

Ver. 14.0.6023.1000 (64 bits)

 

[myall_blues]

Not working as expected, you can notice for example that the value in cell B2 is exactly the same as in the cell D3 for the same client, but when running the formula in E2, it indicates "Not yet" see the attached screenshot.
I've updated the OP with my Excel version.

Just swap the “Not yet” and “Billed” in the formula.

=IF(ISNA(INDEX($A$2:$A$3,MATCH(1,(C2=$A$2:$A$3)*(D2=$B$2:$B$3),0))),"Billed","Not yet")