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

#### javajoe

##### Board Regular
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

### Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
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..

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))

javajoe said:
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?

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

javajoe said:
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")

=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)))

Replies
11
Views
511
Replies
4
Views
343
Replies
3
Views
254
Replies
1
Views
233
Replies
5
Views
872

1,206,814
Messages
6,075,021
Members
446,114
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back