VLOOKUP

[tjott]

Is there any way when a value is not found and Excel returns N/A to put in a blank?

 

[DonkeyOte]

=if(iserror(vlookup(a1,blah,2,false),"",vlookup(a1,blah,2,false))

 

[ChrisUK]

use the isNA function as a front to your vlookup.

=if(isNA(vlookup(Value, Table, Offset, TRUE|FALSE))," ",vlookup(value,table,offset,TRUE|FALSE)))

HTH

Chris

 

[Aladin Akyurek]

On 2002-10-15 09:02, tjott wrote:
Is there any way when a value is not found and Excel returns N/A to put in a blank?

It seems my "war" for efficiency is not that widespread...

A couple of options...

(1.) Select all the cells of the lookup table, excluding the labels that you might have.

(2.) Go to the Name Box on the Formula Bar.

(3.) Type LTable (or something you like better as name) and hit enter.

Now use the following formula with built-in functions...

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

Or, install the morefunc.xll add-in from:

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

and use:

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

These formulas avoid double computations that degrade performance.

 

[DonkeyOte]

ISNA is good but won't cater for all error types (like REF#) though I appreciate only NA was asked for.

 

[applebyd]

I think it's been mentioned before although I can't find the thread.

If you use the ISERROR function you surpress ALL error messages.

This means you can't see if anything else has gone wrong.

Using the ISNA function just kills the most common one for the Vlookup.

Now if I can just find something that surpressed a #DIV/0 without using ISERROR I'll be really happy.

DaveA

Edit: to kill the Jonathon Ross spelling errors
This message was edited by applebyd on 2002-10-15 10:48

 

[Aladin Akyurek]

On 2002-10-15 10:46, applebyd wrote:
I think it's been mentioned before although I can't find the thread.

If you use the ISERROR function you surpress ALL error messages.

This means you can't see if anything else has gone wrong.

Using the ISNA function just kills the most common one for the Vlookup.

Now if I can just find something that surpressed a #DIV/0 without using ISERROR I'll be really happy.

DaveA

Edit: to kill the Jonathon Ross spelling errors
This message was edited by applebyd on 2002-10-15 10:48

Right. The incredible habit/insistence of using ISERROR is also a hallmark of the microsoft newsgroups. Another is not being disturbed at all by computing the same thing multiple times in the same formula.

Regarding #DIV/0!, we can test the denominator before the division takes place...

=IF(Denominator,Nominator/Denomitor,OtherwiseValue)

where each term itself can be a computation. Is this not satisfactoty?

Aladin