IF Statement problem

[JamieDI]

Hi All

Having a brain fade, simply put if my formula returns a number >= 4 I want to make it 3, if its not I want to return the native value. Formula below.

=IF((IFERROR(VLOOKUP($B7,'SharePoint Data SHC'!$B:$GB,Q$4,FALSE),0))>=4,3,IFERROR(VLOOKUP($B7,'SharePoint Data SHC'!$B:$GB,Q$4,FALSE),0))

This should return a score of 1 instead is forced to the value 3.

Any help greatly appreciated.

 

[VeryForgetful]

How about

=IFERROR(IF(VLOOKUP($B7,'SharePoint Data SHC'!$B:$GB,Q$4,FALSE)>=4,3,(VLOOKUP($B7,'SharePoint Data SHC'!$B:$GB,Q$4,FALSE))),"")<strike></strike>

 

[JamieDI]

Hi thanks for helping still returns the 3 instead of the 1 don't know why its not working.......

This is the rule I am trying to apply on a 5 point scale - "Move all responses of 4 & 5 to 3 else return the original score"

 

[VeryForgetful]

Is Q$4 definitely correct as that would only refer to a single cell rather than a column within your range. If the lookup value is anywhere in column Q then you would need to change it to 16 as that would be the 16th column along within the range $B:$GB.

=IFERROR(IF(VLOOKUP($B7,'SharePoint Data SHC'!$B:$GB,16,FALSE)>=4,3,(VLOOKUP($B7,'SharePoint Data SHC'!$B:$GB,16,FALSE))),"")

Failing that can you send a sample through?

 

[JamieDI]

Hi Mate the columns are labeled hence the the defined cell. I'll try the change now. Thanks