Optimize Index Lookup Formula?

[JPARKHURST]

I have a formula which works, but I would like to see if it's as optimized as it could be. I've a new computer and it's either a LOT slower than my older one (very possible), or this forumla needs to be optimized with a better formula or moved to VBA.

I am looking for the last instance of a parts shortage, which are complied in a different worksheet (same job listed concatenates multiple shortages for one display on the last shortage).

=IFERROR(INDEX(Shortages!$N:$N,LOOKUP(2,1/(Shortages!$B:$B=D2),ROW(Shortages!$B:$B))),"")

Thanks in advance,

Jon

 

[Eric W]

First, you can rewrite that a bit more concisely as:

=IFERROR(LOOKUP(2,1/(Shortages!$B:$B=D2),Shortages!$N:$N),"")

but that probably won't speed it up much. The biggest change you can do would be to avoid whole column references, like this:

=IFERROR(LOOKUP(2,1/(Shortages![COLOR=#ff0000]$B1:$B100[/COLOR]=D2),Shortages![COLOR=#ff0000]$N1:$N100)[/COLOR],"")

Figure out the maximum row you expect to use and use that, otherwise, Excel is forced to examine over a million rows, even if you only use 100.

 

[JPARKHURST]

Good catch. I found an error this morning (was only looking for first row, when I had changed my source sheet a week ago). Completely missed the forest for the trees on that one.

Thanks again!

Jon

Oh, yeah, that definitely made all the difference in the world. Thanks again for finding my mistake!

 

[Eric W]

You're welcome!