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.