Jun 06 2022 10:58 PM
I need some guidance
i have got 3 workbooks; export, current workbook and credit limit request workbook.
The logic are as follows:
1. if Business Registration data in both export and credit limit request workbook exist, perform following formula:
(1a)
IFNA(IF(VLOOKUP(B1,[export.xlsx]Sheet1!E:E,1,0)=VLOOKUP(B1,'[Credit Limit Requests (no existing SAP IDs).xlsx]No SAP ID'!$B:$B,1,0),SUMIF([export.xlsx]Sheet1!E:I,B1,Sheet1!I:I)
else
(1b)
VLOOKUP(B1,'[Credit Limit Requests (no existing SAP IDs).xlsx]No SAP ID'!$B:$M,12,0),SUMIF(Sheet1!E:I,B1,[export.xlsx]Sheet1!I:I))
2. if Business Registration data does not exist in export but exist in credit limit request workbook with duplicate Business Registration id, perform following formula:
(2a)
IF(COUNTIF('[Credit Limit Requests (no existing SAP IDs).xlsx]No SAP ID'!$B:$B,'[Credit Limit Requests (no existing SAP IDs).xlsx]No SAP ID'!B2>1),SUMPRODUCT(MAX(--('[Credit Limit Requests (no existing SAP IDs).xlsx]No SAP ID'!$B2:$B1000=B1)*--('[Credit Limit Requests (no existing SAP IDs).xlsx]No SAP ID'!$S2:$S1000=SUMPRODUCT(MAX(--('[Credit Limit Requests (no existing SAP IDs).xlsx]No SAP ID'!$B2:$B1000=B1)*'[Credit Limit Requests (no existing SAP IDs).xlsx]No SAP ID'!$S2:$S1000)))*'[Credit Limit Requests (no existing SAP IDs).xlsx]No SAP ID'!$M2:$M1000))
else
VLOOKUP(B1,'[Credit Limit Requests (no existing SAP IDs).xlsx]No SAP ID'!$B:$M,12,0)
Formula 1a and 1b is working fine but formula 2a and 2b does not populate the correct data.
End of the day i would like to combine both formula 1 and 2 into a single cell as an nested if.
Thanks in advance!