Help with Formula

mutrus

Board Regular
Joined
Sep 10, 2002
Messages
80
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.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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
Joined
Feb 15, 2002
Messages
1,743
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
Joined
Sep 10, 2002
Messages
80
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

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
Joined
Sep 10, 2002
Messages
80
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

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
Joined
Sep 10, 2002
Messages
80
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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.
 

Forum statistics

Threads
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.
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
Top