VLOOKUP and #N/A

[Tiziano]

I use these two methods in order to avoid the #N/A error message in a
VLOOKUP formula:

=IF(ISERROR(VLOOKUP(value,array,index,FALSE)),0,VLOOKUP(value,array,index,FA
LSE))

or

=IF(ISNA(VLOOKUP(value,array,index,FALSE)),0,VLOOKUP(value,array,index,FALSE
))

The problem with these two methods is that VLOOKUP is executed twice,
thus things get slowed down quite a bit in a large table array. Does anyone
have a better solution?

Thanks.

 

[Norman Harker]

Hi Tiziano!

You might get something from:

Charles Williams:
http://www.decisionmodels.com/optspeede.htm#Speed_Lookups

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Aladin Akyurek]

See also:

http://makeashorterlink.com/?J32E23767

 

[Tom Ogilvy]

Countif is less efficient and slower than Vlookup because?

--
Regards,
Tom Ogilvy

See also:

http://makeashorterlink.com/?J32E23767

I use these two methods in order to avoid the #N/A error message in a
VLOOKUP formula:

=IF(ISERROR(VLOOKUP(value,array,index,FALSE)),0,VLOOKUP(value,array,index,FA=IF(ISNA(VLOOKUP(value,array,index,FALSE)),0,VLOOKUP(value,array,index,FALSE

 

[Harlan Grove]

Countif is less efficient and slower than Vlookup because?

....

Presumably VLOOKUP(.,.,.,0) quits searching when it finds the first/topmost
match while COUNTIF needs to go through the entire range argument. If the
range were large, COUNTIF could be lots slower.

The most efficient solution is using two cells, Aladin's option #1. Memory
spent on the second cell is saved by the second formula being much shorter
than the single cell formula.

 

[Aladin Akyurek]

Countif is less efficient and slower than Vlookup because?

Yes, Tom. I subscribe Harlan's explanation.

Here is a speed comparison: CountIf vs IsNumber/Match vs Vlookup:

http://www.mrexcel.com/board2/viewtopic.php?t=40233

Countif is less efficient and slower than Vlookup because?

--
Regards,
Tom Ogilvy

See also:

http://makeashorterlink.com/?J32E23767

=IF(ISERROR(VLOOKUP(value,array,index,FALSE)),0,VLOOKUP(value,array,index,FA=IF(ISNA(VLOOKUP(value,array,index,FALSE)),0,VLOOKUP(value,array,index,FALSE

 

[Tom Ogilvy]

So the true answer is we don't know. Checking for a match portends that
there is a good possibility their won't be. Countif could be quite a bit
faster than Vlookup for the full range or it might not be, but I doubt it is
slower. In any event, Senseless doesn't seem to be approriate. (the
entirerange argument could include a range outside the used range - countif
appears to be smart enough not to check that, don't see an indication that
vlookup is).

 

[Tom Ogilvy]

Thanks,.

And I am sure a different workbook with a different data arrangement could
be designed to give significantly different results. But I appreciate your
insights.

 

[Peo Sjoblom]

Hi Tom,

I have no idea which is faster than the other with different
conditions/arrangements/formats.
There is a drawback using countif as an indicator if match is found as in

if(countif(range,lookup_value),vlookup(etc.

countif makes no distinction between text number and real numbers, assume
there is an import from
some database and the leftmost vlookup table column is supposed to be
numeric but
for some reason the numbers are text. countif will return true regardless
"5" or 5 while
vlookup will return #N/A and since the if(countif is there to prevent that
it is less
good (IMHO) compared to if(isnumber(match)),vlookup(
regardless of the speed

 

[Tom Ogilvy]

I am not advocating Countif as the way to go. I certainly agree that for
most cases, the fact that match and vlookup will stop at the first find
offers an advantage. I just wondered what merited the "Senseless" label for
Countif (and Iserror). You raise another valid point.

 

[Harlan Grove]

So the true answer is we don't know. Checking for a match portends that
there is a good possibility their won't be. . . .

OK, you want to dabble in probabilities. Either there's a match or there
isn't. How often would there need to be no match in order to build in the
machinery necessary to check for no match? More or less than half the time?
Unless there were almost always no matches, VLOOKUP would be faster than
COUNTIF on average if both took the same time to search through the entire
range, but VLOOKUP stopped on the first instance.

. . . Countif could be quite a bit
faster than Vlookup for the full range or it might not be, but I doubt it is
slower. . . .

I'd agree with that. They both accept basically the same wildcards, so it's
a good bet they use some common text comparison code. The only wrinkle is
that COUNTIF provides all comparisons: <, <=, =, <>, >=, =. Implicitly,
VLOOKUP only provides =. That should make COUNTIF slightly slower, but
probably not so much so as to be too noticeable even in a million
iterations.

. . . In any event, Senseless doesn't seem to be approriate. . . .

Fair point, but you didn't bring it up originally.

. . . (the
entirerange argument could include a range outside the used range - countif
appears to be smart enough not to check that, don't see an indication that
vlookup is).

Interesting. Given the speed at which many functions seem to calculate when
given grossly excessive ranges, I wouldn't be surprised if there were logic
built in to many of them that they skip over blank cells. However, I have
nothing to support this impression.

 

[Aladin Akyurek]

Tom,

I thought I said:

QUOTE

5] Reject using either senseless

=IF(ISERROR(VLOOKUP(LookupValue,LookupTable,ColIdx,0)),0,VLOOKUP(LookupValue
,LookupTable,ColIdx,0))

or rather inefficient

=IF(COUNTIF(LookupValue,INDEX(LookupTable,0,1)),VLOOKUP(LookupValue,LookupTa
ble,ColIdx,0),0)"

UNQUOTE

The reading that I expect is: IsError as used here does not make much sense
and CountIf as used here is inefficient (although, like many others, I also
posted them as ways of avoiding #N/A). I really see no point in using
IsError instead of IsNa, except when one wants to look up things in an
error-ridden area. Even then one should be more concerned about the errors
themselves.

While we are at it, I'd rather see that VLOOKUP, HLOOKUP, and MATCH has an
additional optional parameter, ReturnVal, that would allow us to avoid #N/A
when appropriate to do so:

VLOOKUP(LookupValue,LookupTable,ColIdx,<MatchType>,<ReturnVal>)
HLOOKUP(LookupValue,LookupTable,RowIdx,<MatchType>,<ReturnVal>)
MATCH(LookupValue,LookupTable,<MatchType>,<ReturnVal>)

If ReturnVal omitted, default to #N/A, otherwise return ReturnVal instead of
#N/A.

Aladin