If statement with Let formula

[ecrodrig]

So I want to do the following:

1. - If cell A2 says "Closed" then check for the name of cell (E2) in my RFS billing file and if found it needs to give me the information in column AQ of that file
2. - If Column AQ says Complete do what is in step 2a. If E2 is not found then do what is in step 2b. If Column AQ is blank then the output should be "Not fully billed"
2a. - Check for E2 in the FA Billing file and if found give the information in AA. If the information is AA2 matches the information in U2 of the PM file then output should be "Verified in FA file" else it should be "Need to Check TM"
2b. - Check for E2 in the FA Billing file and if found give the information in AA. If the information is AA2 matches the information in V2 of the PM file then output should be "Verified in FA file" else it should be "Need to Check OTC"

This is what I did for step 1:

=IF(A2 = "Closed", FILTER('[RFS_Billing_File.xlsx]RFS T&M'!$AQ:$AQ,('RFS_Billing_File.xlsx]RFS T&M'!$A:$A="Total")*('[RFS_Billing_File.xlsx]RFS T&M'!$B:$B=D2),"Not in T&M file"), "Not Closed")

This is fine as long as A2 is not closed, Where I am stuck on this step is when it is closed I am getting the information on AQ which is "Complete" or I am getting the "Not Found in T&M file" I need to tweak this so that instead of providing that output it moves to the next task.

Maybe a let statement but I don't seem to get the Let statement working correct.

Maybe a Let statement but I am not getting it correct so it's not giving me what I need. This is what I did:

=LET(x,IF(A2 = "Closed", FILTER('[RFS_Billing_File.xlsx]RFS T&M'!$AQ:$AQ,('RFS_Billing_File.xlsx]RFS T&M'!$A:$A="Total")*('[RFS_Billing_File.xlsx]RFS T&M'!$B:$B=D2),"Not in T&M file"), "Not Closed"), If(x="Not in T&M file", Xlookup(E2,'[FA Billing File.xlsx]2021 2022'!$B:$B, '[FA Billing File.xlsx]2021 2022'!$AA:$AA, "Not found in billing file"), "Check")


Here it's not giving me the ones that aren't closed. The first 2 rows should say not closed like in the top picture rather than check.

Any help is appreciated. Thanks

 

[ecrodrig]

So I want to do the following:

1. - If cell A2 says "Closed" then check for the name of cell (E2) in my RFS billing file and if found it needs to give me the information in column AQ of that file
2. - If Column AQ says Complete do what is in step 2a. If E2 is not found then do what is in step 2b. If Column AQ is blank then the output should be "Not fully billed"
2a. - Check for E2 in the FA Billing file and if found give the information in AA. If the information is AA2 matches the information in U2 of the PM file then output should be "Verified in FA file" else it should be "Need to Check TM"
2b. - Check for E2 in the FA Billing file and if found give the information in AA. If the information is AA2 matches the information in V2 of the PM file then output should be "Verified in FA file" else it should be "Need to Check OTC"

This is what I did for step 1:

=IF(A2 = "Closed", FILTER('[RFS_Billing_File.xlsx]RFS T&M'!$AQ:$AQ,('RFS_Billing_File.xlsx]RFS T&M'!$A:$A="Total")*('[RFS_Billing_File.xlsx]RFS T&M'!$B:$B=D2),"Not in T&M file"), "Not Closed")

This is fine as long as A2 is not closed, Where I am stuck on this step is when it is closed I am getting the information on AQ which is "Complete" or I am getting the "Not Found in T&M file" I need to tweak this so that instead of providing that output it moves to the next task.

Maybe a let statement but I don't seem to get the Let statement working correct.


View attachment 75721



Maybe a Let statement but I am not getting it correct so it's not giving me what I need. This is what I did:

=LET(x,IF(A2 = "Closed", FILTER('[RFS_Billing_File.xlsx]RFS T&M'!$AQ:$AQ,('RFS_Billing_File.xlsx]RFS T&M'!$A:$A="Total")*('[RFS_Billing_File.xlsx]RFS T&M'!$B:$B=D2),"Not in T&M file"), "Not Closed"), If(x="Not in T&M file", Xlookup(E2,'[FA Billing File.xlsx]2021 2022'!$B:$B, '[FA Billing File.xlsx]2021 2022'!$AA:$AA, "Not found in billing file"), "Check")


Here it's not giving me the ones that aren't closed. The first 2 rows should say not closed like in the top picture rather than check.


View attachment 75725



Any help is appreciated. Thanks

I think I have steps 1 and 2 figured out. I had to tweak some files in Power Query to avoid the filter option.


=LET( x, IF('Master_Data_File'!A2<> "Closed", "Still In Progress",XLOOKUP(A2, '[RFS_Billing_File.xlsx]T&M_RFS_Billing_Verifing_comple'!$A:$A,'[RFS_Billing_Master.xlsx]T&M_RFS_Billing_Verifing_comple'!$D:$D,"not found")), IF(x="Complete", XLOOKUP(A2,'[FA_billing.xlsx]2021 2022'!$A:$A,'[FA_Billing.xlsx]2021 2022'!$AA:$AA, "Not found in billing file"), "Not Closed"))