Formula construction with ISNA and ISBLANK

Jon Johny

Board Regular
Joined
Sep 15, 2008
Messages
162
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

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
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:
Upvote 0
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.
 
Upvote 0
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")
 
Upvote 0
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)))
 
Upvote 0
=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)))
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,185
Members
448,554
Latest member
Gleisner2

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top