IF ISNA syntax - can't figure it out

[rrmando]

For the love of God please help me. I don't want the #N/A to show. This is the formula but I cannot get the ISNA syntax correct.

=IF(LEN(B9)=4,VLOOKUP("x",D37:F45,3,FALSE),VLOOKUP("x",D27:F35,3,FALSE))

What I came up with....I know I'm close but I'm going crazy.

=IF(isna(LEN(B9)=4,VLOOKUP("x",D37:F45,3,FALSE),VLOOKUP("x",D27:F35,3,FALSE)),"",(LEN(B9)=4,VLOOKUP("x",D37:F45,3,FALSE),VLOOKUP("x",D27:F35,3,FALSE)))

I've tried different variations but not getting it. Thanks.

 

[scottylad2]

What version of Excel are you using? Iferror in 2007 or later =iferror(yourformulahere,"")

 

[Scott Huish]

If an earlier version (I shortened your original formula):

=IF(ISNUMBER(MATCH("x",IF(LEN(B9)=4,D37:D45,D27:D45),0)),VLOOKUP("x",IF(LEN(B9)=4,D37:F45,D27:F45),3,FALSE),"")

 

[scottylad2]

try this if 2003 or earlier

IF(ISNA(IF(LEN(B9)=4,VLOOKUP("x",D37:F45,3,FALSE),VLOOKUP("x",D27:F35,3,FALSE))),"",IF(LEN(B9)=4,VLOOKUP("x",D37:F45,3,FALSE),VLOOKUP("x",D27:F35,3,FALSE)))

 

[rrmando]

Thank you all!!! Sorry, haven't been on here in a while....still on Excel 2003 : ( Thanks again...really appreciate everyone's help.

 

[Aladin Akyurek]

If on Excel 2007 or later...

=IFERROR(VLOOKUP("x",IF(LEN(B9)=4,D37:F45,D27:F35),3,0),"")

On all versions, if a text result is expected...

=LOOKUP(REPT("z",255),CHOOSE({1,2},"",
    VLOOKUP("x",IF(LEN(B9)=4,D37:F45,D27:F35),3,0)))

Otherwise, see, e.g., Hotpepper's suggestion.