return null values from vlookup

[stewbrown]

I would like to have vlookup return a null or missing (or empty cell) when it does not find a match, instead of the "#N/A". Is there a way to do this?

 

[XL-Dennis]

stewbrown,

One way is to use the ISERROR-function.

=IF(ISERROR(VLOOKUP(......)),"",VLOOKUP(.....))

Kind regards,
Dennis
This message was edited by XL-Dennis on 2002-09-24 10:49

 

[maxflia10]

if(iserror(vlookup(your formula),"",vlookup(your formula))

Oops, Sorry Dennis! Didn't see you!
This message was edited by maxflia10 on 2002-09-24 10:50

 

[XL-Dennis]

maxflia,

Hehe, look at the time we had :wink:

Posted: 2002-09-24 10:49

Kind regards,
Dennis

 

[Aladin Akyurek]

On 2002-09-24 10:13, stewbrown wrote:
I would like to have vlookup return a null or missing (or empty cell) when it does not find a match, instead of the "#N/A". Is there a way to do this?

One of:

[1]

=IF(ISNUMBER(MATCH(lookup-value,INDEX(LTable,0,1),0)),VLOOKUP(lookup-value,LTable,ColumnIdx,0),"")

[2]

=IF(ISNA(SETV(VLOOKUP(lookup-value,LTable,ColumnIdx,0))),"",GETV())


The formula in [2] requires the morefunc.xll add-in, downloadable from:

http://longre.free.fr/english/index.html

 

[shades]

Regarding "Null Returns," I have this formula:

=INDEX(Work!$B$1:$F$823, MATCH($D8,Work!$D$1:$D$823,), MATCH("SOV",Work!$B$1:$F$1,))

which returns #N/A in some situations. I have tried the advice in this list to avoid #N/A but XL (2002) notes that I have too many arguments or an invalid argument. Here is what I tried:

=IF(ISNUMBER(INDEX(Work!$B$1:$F$823, MATCH($D8,Work!$D$1:$D$823,), MATCH("SOV",Work!$B$1:$F$1,)),"")

But, obviously I'm doing something wrong. I'm either too old, too slow, or too confused to figure it out.

Any help is appreciated.

 

[Aladin Akyurek]

On 2002-10-08 16:20, shades wrote:
Regarding "Null Returns," I have this formula:

=INDEX(Work!$B$1:$F$823, MATCH($D8,Work!$D$1:$D$823,), MATCH("SOV",Work!$B$1:$F$1,))

which returns #N/A in some situations. I have tried the advice in this list to avoid #N/A but XL (2002) notes that I have too many arguments or an invalid argument. Here is what I tried:

=IF(ISNUMBER(INDEX(Work!$B$1:$F$823, MATCH($D8,Work!$D$1:$D$823,), MATCH("SOV",Work!$B$1:$F$1,)),"")

But, obviously I'm doing something wrong. I'm either too old, too slow, or too confused to figure it out.

Any help is appreciated.

Shades,

I expect your original formula to be:

Either...

=INDEX(Work!$B$1:$F$823,MATCH($D8,Work!$D$1:$D$823,0),MATCH("SOV",Work!$B$1:$F$1,0))

Or...

=INDEX(Work!$B$1:$F$823,MATCH($D8,Work!$D$1:$D$823),MATCH("SOV",Work!$B$1:$F$1,0))

Which one is it?

Would it be possible for you to install the morefunc.xll add-in?

Aladin
This message was edited by Aladin Akyurek on 2002-10-08 16:55

 

[njbeancounter]

Thanks to all!! Using the 'iserror' function solved my problem, and i didn't even ask the question!

 

[PaddyD]

NJ,

as a general rule, you should try to avoid using iserror to mask error values in vlookups (and, indeed, other functions):

a) it requires the double calculation of the vlookup, which is inefficient
b) it masks all error values, which is inappropriate.

If you insist on using an error function, you should at least check for the specific error...

Paddy

 

[shades]

On 2002-10-08 16:30, Aladin Akyurek wrote:

Shades,

I expect your original formula to be:

Either...

=INDEX(Work!$B$1:$F$823,MATCH($D8,Work!$D$1:$D$823,0),MATCH("SOV",Work!$B$1:$F$1,0))

Or...

=INDEX(Work!$B$1:$F$823,MATCH($D8,Work!$D$1:$D$823),MATCH("SOV",Work!$B$1:$F$1,0))

Which one is it?

Would it be possible for you to install the morefunc.xll add-in?

Actually, it was neither, what I posted is in the formula.

=INDEX(Work!$B$1:$F$823, MATCH($D8,Work!$D$1:$D$823,), MATCH("SOV",Work!$B$1:$F$1,)).

I had set it up using the Lookup Wizard. As far as working correctly, it gives the correct values (whenever there are values in the table). Will the missing zeroes help?

As for the morefunc - I have it and it is installed, but not sure if it requires payment for it to work. How would I go about using it?

_________________
- old, slow, and confused
... but at least I'm inconsistent -
This message was edited by shades on 2002-10-08 17:45