Way to prevent ISERROR from slowing down INDEX/MATCH??

[javajoe]

Good morning everyone.


I have a fairly large spreadsheet which uses INDEX/MATCH to perform a variety of lookups on each row. Unfortunately, when I added an IF/ISERROR to the front of each equation to see if a result is found in the lookup (see below), it significantly and very noticeably slows down the entire workbook, and sometimes even hangs at NOT RESPONDING. This is on a computer with a fast processor and 1 gig of memory.

My question is this... is there any other way to make this formula faster, while achieving similar results? The only thing I have figured out is to eliminate the IF/ISERROR function (thus cutting the lookups in half), but I really need that functionality if possible.


Here is my formula:

=IF(ISERROR((INDEX(refZIPZONE!$A$2:$H$10000,MATCH($J2,refZIPZONE!$A$2:$A$10000,0),7))),"OUT OF AREA",(INDEX(refZIPZONE!$A$2:$H$10000,MATCH($J2,refZIPZONE!$A$2:$A$10000,0),7)))


I found a few other posts on the topic but didn't find anything with a better alternative. Any help would be greatly appreciated!

Thanks!!

DAN

 

[al_b_cnu]

Hi,

does this link help?

http://www.mrexcel.com/board2/viewtopic.php?t=167220&highlight=stephen+dunn

 

[javajoe]

Interesting... I will definitely try VLOOKUP, but I thought VLOOKUP was more ineffecient than INDEX/MATCH, which is why I switched to those functions.

Are you recommending replacing INDEX/MATCH with VLOOKUP, or just as a test for the lookup values on the front side?

Thanks for the clarification..

 

[al_b_cnu]

Hi,

Sorry, I was referring to the V function - here's a better example:
http://www.mrexcel.com/board2/viewtopic.php?t=72702&highlight=stephen+dunn

 

[Domenic]

Does this help?

=IF(ISNUMBER(MATCH($J2,refZIPZONE!$A$2:$A$10000,0)),"OUT OF AREA",INDEX(refZIPZONE!$A$2:$H$10000,MATCH($J2,refZIPZONE!$A$2:$A$10000,0),7))

 

[Aladin Akyurek]

Good morning everyone.


I have a fairly large spreadsheet which uses INDEX/MATCH to perform a variety of lookups on each row. Unfortunately, when I added an IF/ISERROR to the front of each equation to see if a result is found in the lookup (see below), it significantly and very noticeably slows down the entire workbook, and sometimes even hangs at NOT RESPONDING. This is on a computer with a fast processor and 1 gig of memory.

My question is this... is there any other way to make this formula faster, while achieving similar results? The only thing I have figured out is to eliminate the IF/ISERROR function (thus cutting the lookups in half), but I really need that functionality if possible.


Here is my formula:

=IF(ISERROR((INDEX(refZIPZONE!$A$2:$H$10000,MATCH($J2,refZIPZONE!$A$2:$A$10000,0),7))),"OUT OF AREA",(INDEX(refZIPZONE!$A$2:$H$10000,MATCH($J2,refZIPZONE!$A$2:$A$10000,0),7)))


I found a few other posts on the topic but didn't find anything with a better alternative. Any help would be greatly appreciated!

Thanks!!

DAN

Is refZIPZONE!$A$2:$H$10000 sorted on column A in ascending order? If not, are you able to sort the data area on column A in ascending order and keep it sorted?

 

[javajoe]

Yes.... column A in refZIP-ZONE is permanently sorted A>Z, if that clarifies things for you. It's a list of all zip codes in the country, and my spreadsheet cross-references a consumer's zip code a pre-defined list of our internal zones.

DV

 

[Aladin Akyurek]

Yes.... column A in refZIP-ZONE is permanently sorted A>Z, if that clarifies things for you. It's a list of all zip codes in the country, and my spreadsheet cross-references a consumer's zip code a pre-defined list of our internal zones.

DV

Try:

=IF(LOOKUP($J2,refZIPZONE!$A$2:$A$10000)=$J2,LOOKUP(refZIPZONE!$A$2:$A$10000,refZIPZONE!$G$2:$G$10000),"OUT OF AREA")

instead of:

=IF(ISERROR((INDEX(refZIPZONE!$A$2:$H$10000,MATCH($J2,refZIPZONE!$A$2:$A$10000,0),7))),"OUT OF AREA",(INDEX(refZIPZONE!$A$2:$H$10000,MATCH($J2,refZIPZONE!$A$2:$A$10000,0),7)))