Can't figure out why this formula isn't working...

[sharkey]

=IFERROR(IF($E9="","",IF(AND(VLOOKUP($A9,PIVOTS.LOOKUPS!$BP:$BP,1,FALSE)=$A9,VLOOKUP($A9,PIVOTS.LOOKUPS!$BS:$BS,1,FALSE)=$A9),"DP & KM",IF(VLOOKUP($A9,PIVOTS.LOOKUPS!$BP:$BP,1,FALSE)=$A9,"CONVERT TO KM",IF(VLOOKUP($A9,PIVOTS.LOOKUPS!$BS:$BS,1,FALSE)=$A9,"CONVERT TO DP","")))),"")


This formula is made up of two parts:


1) The first part checks to see if a number is present in two different columns, BP and BS, and if it is it returns “DP & KM”.


IF(AND(VLOOKUP($A9,PIVOTS.LOOKUPS!$BP:$BP,1,FALSE)=$A9,VLOOKUP($A9,PIVOTS.LOOKUPS!$BS:$BS,1,FALSE)=$A9),"DP & KM",


2) The second part, which should only happen if the first part isn’t true, checks each column individually and returns the associated value.


IF(VLOOKUP($A9,PIVOTS.LOOKUPS!$BP:$BP,1,FALSE)=$A9,"CONVERT TO KM",IF(VLOOKUP($A9,PIVOTS.LOOKUPS!$BS:$BS,1,FALSE)=$A9,"CONVERT TO DP",""))

 

[sgmpatnaik]

@sharkey


Hi


What type of error message you will receive

 

[NARAYANK991]

Hi ,


What is the problem you are facing ?


The formula will return a valid result only if both BP and BS columns have the value that is in A9 ; this is because the second and third IF statements will never be evaluated in the event the AND statement returns an error ; you need to rewrite the IF statement on the lines of the following :


=IF($E9="","",IF(ISNA(VLOOKUP($A9,PIVOTS.LOOKUPS!$BS:$BS,1,FALSE)),IF(ISNA(VLOOKUP($A9,PIVOTS.LOOKUPS!$BP:$BP,1,FALSE)),"","CONVERT TO DP"),IF(ISNA(VLOOKUP($A9,PIVOTS.LOOKUPS!$BP:$BP,1,FALSE)),"CONVERT TO KM","DP & KM")))


Please note that this may not be exactly what you want ; the CONVERT TO DP and the CONVERT TO KM might need to be switched. Please check.


Narayan

 

[sharkey]

Thanks Narayan! It works perfectly. You = awesome.