prevent #N/A

You can do this by adding a simple if(isna) function to your formula. It should go something like this.

=if(isna(<put the vlookup formula here>),"",<put vlookup formula here>)

This will tell excel that if the result of the vllokup is #N/A then put blank ("") in the cell. If it is not #N/A then put the reult of the vlookup in the cell.

Hope this helps

Jacob

You can do this by adding a simple if(isna) function to your formula. It should go something like this.

=if(isna(<put the vlookup formula here>),"",<put vlookup formula here>)

This will tell excel that if the result of the vllokup is #N/A then put blank ("") in the cell. If it is not #N/A then put the reult of the vlookup in the cell.

Hope this helps

Jacob

Ignor the top two responces my message was not posted completely

You can do this by adding a simple if(isna) function to your formula. It should go something like this.

=if(isna(<put the vlookup formula here>),"",<put vlookup formula here>)

This will tell excel that if the result of the vllokup is #N/A then put blank ("") in the cell. If it is not #N/A then put the reult of the vlookup in the cell.

Hope this helps

Jacob

Ignor the top two responces my message was not posted completely

You can do this by adding a simple if(isna) function to your formula. It should go something like this.

if(isna(<put the vlookup formula here>),"",<put vlookup formula here>)

This will tell excel that if the result of the vllokup is #N/A then put blank ("") in the cell. If it is not #N/A then put the reult of the vlookup in the cell.

Hope this helps

Jacob

This one should be right

You can do this by adding a simple if(isna) function to your formula. It should go something like this.

if(isna("put the vlookup formula here"),"","put vlookup formula here")

This will tell excel that if the result of the vllokup is #N/A then put blank ("") in the cell. If it is not #N/A then put the reult of the vlookup in the cell.

Hope this helps

Jacob

M.A., if you only want "the cell to appear blank
instead of #N/A..." you can apply a conditional
format using a Formula Is condition with the
expression =ISNA(ref) where "ref" is the cell
reference to the cell containing your VLOOKUP()
function. When this expression is TRUE display a \
font color that matches the pattern color (most
likely white) of the cell.

This approach is more efficient that evaluating
your VLOOKUP() twice -- once just to see if it
will return a #N/A error value, and then the
2nd time to return the value you were seeking
in the 1st place.

Am interested in learning how to achieve efficiencies where possible. In what way is your method more efficient? (Less memory usage? Measurably faster processing?). Is it possible to quantify the increased efficiency?

Re: This one should be right

thank you jacob,that seems to have done the trick!!

> In what way is your method more efficient?
> (Less memory usage? Measurably faster
> processing?). Is it possible to quantify the
> increased efficiency?

I begin by counting discete operations on a
worse case scenario. This approach is
system independent, but provides some basis
for estimating processing time. In order to
accomplish this one needs to be somewhat
familiar with the nature of Excel's functions
and how they operate. For example, VLOOKUP()
performs a sequential search of a list. If
it's optional, 4th argument, "range_lookup", is
FALSE (or 0) then it will examine the entire
list when searching for a non-existent
lookup_value.

So let's look at a simple test case and compare
the 2 approaches. Let's setup a table_array
in cells A1:B4 to be...

{"110",1;"120",2;"130",3;"140",4;"150",5}

Let's enter a lookup_values, ("140";"150"}, in
cells E1:E2, and examine the performance of the
formula, =IF(ISNA(VLOOKUP(E1,$A$1:$B$5,2,0)),"",VLOOKUP(E1,$A$1:$B$5,2,0)).

For the 1st lookup_value this formula
will examine the first 4 rows of the lookup_table
and then test the ISNA() condition for each.
Since "140" is present in the lookup_table, the
IF() function will direct a 2nd pass thru the
lookup_table to return the value, 4. So, let's
see... 4 rows in the lookup_table are examined
twice (that's 8 table accesses). During the 1st
pass thru the table 4 ISNA() evaluations are
performed. The IF() function is a branching
instruction, but let's not consider it for this
analysis.

Table accesses = 8
ISNA() tests = 4

Since my approach only involves 1 call to the
VLOOKUP() function it performs like this:

Table accesses = 4
ISNA() tests = 1 (used by Conditional Formatting
or SUMIF())

Many times users resort to this
=IF(ISNA(VLOOKUP()),"",VLOOKUP()) because they're
trying to prevent the propogation of error
values to arithmetic operations. It is
considerable more efficient (and flexible)
to defer the ISNA() evaluation to the arithmetic
operation by using a function such as
=SUMIF(ref,"<>#N/A").

Another way to improve lookup efficiency is to
"guarantee" that the lookup_table will always be
is sorted order. This allows you to use
{VLOOKUP(E1,$A$1:$B$5,{1,2},TRUE)}. Now, when looking
for the value "125" it will stop after accessing
the 2nd row. Note that this array formulation
of the VLOOKUP() function is returning 2 columns
from the table. There is no "access" penalty for
returning more that one column. You can then
use this additional information returned by the
VLOOKUP() function to determine if your got the
results you needed. Granted this approach requires
more cells to hold and evaluate the results of
the VLOOKUP() function. It's a tradeoff between
performance and size. Most spreadsheet design
decisions can be distilled to such a tradeoff
consideration.

You can also "tune" your lookup tables so that
commonly encountered values are listed near
the top of a lookup_table. Since the table
will no longer be in sorted order you must
specify FALSE (or 0) as the 4th optional
argument to the VLOOKUP() function, but this
may prove to be more efficient then a TRUE
range_lookup.

My earlier assessment of number of ISNA() evaluations
for =IF(ISNA(VLOOKUP(E1,$A$1:$B$5,2,0)),"",VLOOKUP(E1,$A$1:$B$5,2,0))
was incorrect. There would only be 1 per lookup_value. Thus,
the relative efficiency of the two approaches would be
governed primarily by the VLOOKUP() "double-whamy".
This performance difference is a function of the
ratio, (# of table rows)/(# of lookup_values), and
increases as this ratio increases. Looking up a
single value against a 1 row table using
=IF(ISNA(VLOOKUP(E1,$A$1:$B$5,2,0)),"",VLOOKUP(E1,$A$1:$B$5,2,0))
would require 1.5x the number of operations. Using
the same formula for a single lookup against a 65,000
row table would require nearly 2x the number of
operations.

Actual time difference

Just for information.
With a table of 20,000 rows, 100 look-up values, and with the look-up values appearing in rows 19,901 to 20,000, the actual processing times for the two formula examples were :
=VLOOKUP(E1,$A$1:$B$20000,2,0) - Took 2 seconds
=IF(ISNA(VLOOKUP(E1,$A$1:$B$20000,2,0)),"",VLOOKUP(E1,$A$1:$B$65000,2,0)) - Took 4 seconds

For fewer look-up values, or where the look-up values appear earlier in the table, the time difference is often not measurable (i.e. the time for each formula is less than one second).

Re: Actual time difference

There are a number of considerations that would result in different observed performance of a given formulation. The most obvious is machine performance. The length of a text lookup_value would also effect your observation. It is for these reasons that one should consider theoretically performance of different formulations. Computer scientists evaluate various sort algorithms in this fashion. Bottom-line: there's a 2x performance hit for using a formula such as =IF(ISNA(VLOOKUP(),"",VLOOKUP()).