Vlookup with IF(ISERROR... or is there a better way?

[herkiefan]

Hi everyone.

Hopefully an easy one...

I am trying to utilize the Vlookup function where some of the lookup values do not exist in the table array. This of course results in #N/A. The values being looked up are then added together. The #N/A's can not be added and thus I have several SUM's that equal #N/A.

I can get the calculations to work by using: IF(ISERROR(Lookup),0,Lookup)

Since I have over 15,000 lookup values and I am searching a table array with 5,000 rows, using this function bogs the workbook down.

Is there a better or more efficient function out there...some magic trick I don't know about yet?

If not, I can deal with it.

Thanks,

Mike

“Only two things are infinite, the universe and human stupidity, and I'm not sure about the former.”-Albert Einstein

 

[SkipVought]

Hi,

I always use the ISNA() function.

Also, I rarely use VLOOKUP any more. Rather INDEX and MATCH, since I often lookup values in a column other than the FIRST in the table AND I ALWAYS use Named Column Ranges, via Insert > Name > Create - Create names in top row
[tt]
=if(isna(match(f2,LookupRangeName,0)),0,index(ReturnRangeName,match(f2,LookupRangeName,0),1))
[/tt]
It looks like more work, but it's much more intuitive and explanatory to me.

Here's a working example...
[tt]
=IF(ISNA(MATCH(A4,TOOLNO,0)),"",INDEX(TCD,MATCH(A4,TOOLNO,0),1))
[/tt]


Skip,

_{When a wee mystic is on the loose..
It's a Small Medium at Large!}

 

[AnotherHiggins]

Just speaking for myself, I use
[tab]=If(ISNA(VLookup()),"",VLookup())

(Using ISNA instead of the more general ISERROR and returning an apparently empty cell instead of a zero.)

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[AnotherHiggins]

I was a little slow on the draw there. Skip wins again....

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

Down here in Texas, we're quick on the draw, son!

Skip,

_{When a wee mystic is on the loose..
It's a Small Medium at Large!}

 

[herkiefan]

I appreciate both of your responses. Thanks for the help guys.

“Only two things are infinite, the universe and human stupidity, and I'm not sure about the former.”-Albert Einstein

 

[AnotherHiggins]

TEXAS?!?

Only two things come from Texas....

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

Nukler ;-)

Skip,

_{When a wee mystic is on the loose..
It's a Small Medium at Large!}