# Formula construction with ISNA and ISBLANK

#### Jon Johny

##### Board Regular
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.

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

#### mikerickson

##### MrExcel MVP
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.

Last edited:

#### Jon Johny

##### Board Regular
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

##### Well-known Member
Ok, could you post some sample data with expected result(s)...

##### MrExcel MVP
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:
Code:
``````=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...
Code:
``````=IF(ISNA(VLOOKUP(\$A3,Range1,1,0)),
IF(VLOOKUP(\$A3,Range1,6,0)="",
"NA",
VLOOKUP(\$A3,Range1,6,0)),
"NA")``````

#### tootz2007

##### New Member
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

##### New Member
=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)))

Replies
10
Views
314
Replies
8
Views
94
Replies
1
Views
280
Replies
4
Views
241
Replies
3
Views
222

### Forum statistics

1,191,060
Messages
5,984,414
Members
439,885
Latest member
Akshay1 ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back