Help with Formula

[mutrus]

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

 

[Aladin Akyurek]

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]

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]

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 ("")

 

[Aladin Akyurek]

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]

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

 

[Aladin Akyurek]

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]

Aladin Akyurek

Appreciate your last remarks. Referring to your previous comment:

"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

 

[Aladin Akyurek]

On 2002-11-07 15:37, mutrus wrote:
Aladin Akyurek

Appreciate your last remarks. Referring to your previous comment:

"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]

Thanks - works a treat