# Help with Formula

#### mutrus

##### Board Regular
I am using

IF(ISNA(VLOOKUP(C37,'Sep-02'!C:D,2FALSE)),"",VLOOKUP(C37,'Sep-02'!C:D,2,FALSE))

to return a value or "" is result is #NA

I need to be able to also check if VLOOKUP returns an empty value as well as #NA

How can I do this please

### Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

##### MrExcel MVP
On 2002-11-04 14:26, mutrus wrote:
I am using

IF(ISNA(VLOOKUP(C37,'Sep-02'!C:D,2FALSE)),"",VLOOKUP(C37,'Sep-02'!C:D,2,FALSE))

to return a value or "" is result is #NA

I need to be able to also check if VLOOKUP returns an empty value as well as #NA

How can I do this please

In which cell is this formula and what must happen when "" is returned and when #N/A is returned?

#### IML

##### MrExcel MVP
On 2002-11-04 14:26, mutrus wrote:
I am using

IF(ISNA(VLOOKUP(C37,'Sep-02'!C:D,2FALSE)),"",VLOOKUP(C37,'Sep-02'!C:D,2,FALSE))

to return a value or "" is result is #NA

I need to be able to also check if VLOOKUP returns an empty value as well as #NA

How can I do this please

If zero is not a possible answer, you could use
=SUBSTITUTE(IF(ISNUMBER(MATCH(C37,'SEP-02'!\$C\$1:\$C\$100,0)),VLOOKUP(C37,'SEP-02'!\$C\$1:\$D\$100,2,0),""),0,"")

What values are housed in Column D - are they numbers or text?

#### mutrus

##### Board Regular
Sheet Sep-02 contains

Name 1 Date 1
Name 2 Date 2
Name 3
Name 4 Date 4

The formula is in a cell on another sheet so if it is looking for Name 1 then it returns and displays Date 1, if looking for Name 5 it returns #NA and displays "".

If looking for Name 3 it returns and displays 00/01/00 even though there is no date against Name 3. If this is the case then I would like the result cell to also be blank ("")

##### MrExcel MVP

On 2002-11-04 14:49, mutrus wrote:
Sheet Sep-02 contains

Name 1 Date 1
Name 2 Date 2
Name 3
Name 4 Date 4

The formula is in a cell on another sheet so if it is looking for Name 1 then it returns and displays Date 1, if looking for Name 5 it returns #NA and displays "".

If looking for Name 3 it returns and displays 00/01/00 even though there is no date against Name 3. If this is the case then I would like the result cell to also be blank ("")

Actually, you should try to fill in the lookup table with appropriate values...

Anyway, you could use the following set up:

In D37 enter:

=IF(OR(ISNA(E37),LEN(E37)=0),"",E37)

In E37 enter:

=VLOOKUP(C37,'Sep-02'!C:D,2,0)

The above is the fastest solution possible.

Another option is to install the morefunc.xll add-in and use the single formula...

=IF(ISNA(SETV(VLOOKUP(C37,'Sep-02'!C:D,2,0))),"",IF(LEN(GETV()),GETV(),""))
This message was edited by Aladin Akyurek on 2002-11-04 15:19

#### mutrus

##### Board Regular
I've managed to solve it by changing my original formula to

IF(ISNUMBER(VLOOKUP(C37,'Sep-02'!C:D,2,0)),VLOOKUP(C37,'Sep-02'!C:D,2,0),"")

Thanks to everybody who replied and got me thinking of other ways to resolve the issue

##### MrExcel MVP

On 2002-11-04 20:30, mutrus wrote:
I've managed to solve it by changing my original formula to

IF(ISNUMBER(VLOOKUP(C37,'Sep-02'!C:D,2,0)),VLOOKUP(C37,'Sep-02'!C:D,2,0),"")

Thanks to everybody who replied and got me thinking of other ways to resolve the issue

Two remarks...

1) It computes the same thing twice, thus expensive in performance.
2) It's too specific: The ISNUMBER test works here just because the data to be retrieved is numeric -- with text data it will fail.

#### mutrus

##### Board Regular

"In D37 enter =IF(OR(ISNA(E37),LEN(E37)=0),"",E37)

and in E37 have =VLOOKUP(C37,'Sep-02'!C:D,2,0)

This only party works. I get the right result if E37 value is a valid date or a empty value. However is E37 returns #NA then this I get #NA in D37 not "" as expected

##### MrExcel MVP
On 2002-11-07 15:37, mutrus wrote:

"In D37 enter =IF(OR(ISNA(E37),LEN(E37)=0),"",E37)

and in E37 have =VLOOKUP(C37,'Sep-02'!C:D,2,0)

This only party works. I get the right result if E37 value is a valid date or a empty value. However is E37 returns #NA then this I get #NA in D37 not "" as expected

You're right... I apologize. Change the flawed

=IF(OR(ISNA(E37),LEN(E37)=0),"",E37)

to

=IF(ISNA(E37),"",IF(E37=0,"",E37))

Note that this formula will not differentiate between a real 0 and a 0 that comes about when the lookup value doesn't have a value associated with it.

#### mutrus

##### Board Regular
Thanks - works a treat

Replies
2
Views
98
Replies
8
Views
119
Replies
4
Views
95
Replies
2
Views
154
Replies
21
Views
292

1,148,282
Messages
5,745,836
Members
423,981
Latest member
ph1l

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