Formula construction with ISNA and ISBLANK

[Jon Johny]

Is it possible to combine a IF(ISBLANK and IF(ISNA in a formula? - by way of OR statement

This is the following formula I have:
IF(ISNA(VLOOKUP($A3,Range1,,FALSE)),"NA",VLOOKUP($A3,Range1,6,FALSE))

I would like to put the ISBLANK function in it, but not quite sure how to construct properly.

 

[mikerickson]

What do you want to test the blankness of? $A3 or VLOOKUP($A3,Range1,6,False) and what do you want returned if it is blank?

Also, is there a reason that you are substituting the text "NA" for the error #N/A?

Also, also do you expect a number or text to be returned from the VLOOKUP, perhaps the formula
=IF(ISNUMBER(VLOOKUP(...)),VLOOKUP(...),"NA") would do what you want.

 

[Jon Johny]

I am using a vlookup to find a value for a corresponding category. Sometimes the category I am looking up is not present, therefore ISNA works nicely, but sometimes the category is present, however there is nothing filled out in the blank cell (blank). Therefore instead of my vlookup pulling up 0.00, I would ratehr it assign "NA", if the categories corresponding cell is Blank.

Much appreciated.

 

[shemayisroel]

Ok, could you post some sample data with expected result(s)...

 

[Aladin Akyurek]

I am using a vlookup to find a value for a corresponding category. Sometimes the category I am looking up is not present, therefore ISNA works nicely, but sometimes the category is present, however there is nothing filled out in the blank cell (blank). Therefore instead of my vlookup pulling up 0.00, I would ratehr it assign "NA", if the categories corresponding cell is Blank.

Much appreciated.

It's often better to explain an objective in words than in terms of a formula when looking for help. That said...

Let D2:I100 house the data of interest.

IF(ISNA(VLOOKUP($A3,Range1,,FALSE)),"NA",VLOOKUP($A3,Range1,6,FALSE))

Assuming that we need to work with versions prior Excel 2007:

=IF(ISNUMBER(MATCH($A3,$D$2:$D$100,0)),
   IF(INDEX($I$2:$I$100,MATCH($A3,$D$2:$D$100,0))="",
     "NA",
     INDEX($I$2:$I$100,MATCH($A3,$D$2:$D$100,0))),
   "NA")

Put in terms of VLOOKUP and the named range Range1...

=IF(ISNA(VLOOKUP($A3,Range1,1,0)),
   IF(VLOOKUP($A3,Range1,6,0)="",
     "NA",
     VLOOKUP($A3,Range1,6,0)),
   "NA")

 

[tootz2007]

Is it possible to combine a IF(ISBLANK and IF(ISNA in a formula? - by way of OR statement

This is the following formula I have:
IF(ISNA(VLOOKUP($A3,Range1,,FALSE)),"NA",VLOOKUP($A3,Range1,6,FALSE))

I would like to put the ISBLANK function in it, but not quite sure how to construct properly.

=try to use this formula
IF(ISNA(VLOOKUP($A3,Range1,,FALSE))=true,"",IF(ISBLANK(VLOOKUP($A3,Range1,,FALSE)),VLOOKUP($A3,Range1,,FALSE)))

 

[tootz2007]

=try to use this formula
IF(ISNA(VLOOKUP($A3,Range1,,FALSE))=true,"",IF(ISBLANK(VLOOKUP($A3,Range1,,FALSE)),VLOOKUP($A3,Range1,,FALSE)))

Rev.1

IF(ISNA(VLOOKUP($A3,Range1,,FALSE))=true,"",IF(ISBLANK(VLOOKUP($A3,Range1,,FALSE)),"",VLOOKUP($A3,Range1,,FALSE)))