Correcting condition on IF formula

[Yecart77]

Hi All,

There is a DropBox link below containing my sample Excel file, please let me know if it doesn't work.

DropBox Link

This is the formula I am working on, I am going cross eyed trying to finish it off.

=IF(ISERROR(MATCH(F1079,'Forms Sum'!D:D,0))=FALSE,IF(LEFT(OFFSET('Forms Sum'!$H$1,MATCH(F1079,'Forms Sum'!D:D,0)-1,0),2)<>"No",IF(OFFSET('Forms Sum'!$H$1,MATCH(F1079,'Forms Sum'!D:D,0)-1,0)=IF(LEFT(R1079,3)="WBS",MID(R1079,5,LEN(R1079)-4),IF(LEFT(S1079,3)="WBS",MID(S1079,5,LEN(S1079)-4),"")),"",IF(OR(LEFT(R1079,3)="WBS",LEFT(S1079,3)="WBS"),"INCORRECT WBS","NEEDS WBS")),IF(OR(LEFT(R1079,1)="W",LEFT(S1079,1)="W"),"REMOVE WBS","")),"")

The conditions are as follows:

1.If the value of R1079 or S1079 match exactly to Column H of 'Forms Sum' sheet, return empty cell.

2.If there is a value in R1079 or S1079 that does not match Column H of 'Forms Sum' sheet, return text string ''Incorrect WBS".

3.If there is a value in R1079 or S1079 and the Column H of 'Forms Sum' sheet is empty, return text string "Remove WBS".

4.If there is no value in R1079 or S1079 and there is a value in Column H of 'Forms Sum' sheet, return text string "Needs WBS".

Conditions 1, 2 and 4 and returning the correct responses but Condition 3 is returning "Incorrect WBS" where I want it to return "Remove WBS".

Thank you in advance to anyone who can help me out.

Cheers,
Tracey

 

[SanjayGMusafir]

What is current version of excel? Actually you have marked almost all versions of Excel...

 

[jorismoerings]

Hi Tracey,

Can you elaborate a bit on the criteria because your given formula is modifying data and in some cases the criteria working contradictory to each other.
As an example:

1. which criteria takes precedence if R1079 is an exact match to a value in Column H and S1079 is blank or vice versa
2. does "exact match" in criteria 1 mean with or without the addition of "WBS"

 

[Yecart77]

What is current version of excel? Actually you have marked almost all versions of Excel...

Hi Sanjay, This file is in Excel 2016 but reproduced in Excel 365 for this forum.

 

[Yecart77]

Hi Tracey,

Can you elaborate a bit on the criteria because your given formula is modifying data and in some cases the criteria working contradictory to each other.
As an example:

1. which criteria takes precedence if R1079 is an exact match to a value in Column H and S1079 is blank or vice versa
2. does "exact match" in criteria 1 mean with or without the addition of "WBS"

Hi,

1. In the data export the "WBS' prefixed criteria can appear in Column R or Column S. Only pursuing text strings that start with 'WBS.
2. For context, 'Front Page' is accounting transaction and 'Forms Sum' is a tracker that defines what codes should be attached. The formula demonstrating Condition 1 is on the 'Front Page' sheet, cell N1079. It matches the 'Form Number between Front Page/F1079 and Forms Sum/Column D. When there is a match it goes on to check the 'WBS' criteria. If there is a text string starting with 'WBS' in Column R or S it disregards the first 4 characters and reads the remaining text. eg in cell S1079 it is comparing 'ABC-01' to cell H2 'ABC-01'. As these are an exact match the response is an empty cell as in N1079.

The formula and outcome is demonstrated in N1079-N1082. I'm just trying to correct N1081.

Thank you for having a look at my problem. I appreciate it.

Cheers, Tracey

 

[jorismoerings]

Hi,

Reason for asking was clear to me: your formula will never yield a correct result if the precedence issues in the criteria's aren't fixed.

1.If the value of R1079 or S1079 match exactly to Column H of 'Forms Sum' sheet, return empty cell.

2.If there is a value in R1079 or S1079 that does not match Column H of 'Forms Sum' sheet, return text string ''Incorrect WBS".

3.If there is a value in R1079 or S1079 and the Column H of 'Forms Sum' sheet is empty, return text string "Remove WBS".

4.If there is no value in R1079 or S1079 and there is a value in Column H of 'Forms Sum' sheet, return text string "Needs WBS".

as an example assume in Cell R1079 a value is found but it's not an exact match and Cell S1079 is empty.

At criteria 1:

1. the formula finds a value at R1079 but not an exact match in column H so the first part of the OR is false,
2. the formula finds an empty cell at Cell S1079 empty however if the value in column H is also empty, this part of the OR is TRUE

hence the formula will show a empty cell and stops at the result thus will never reach criteria 3.