Optimizing Index / Match function

[cmotes]

I am looking for ways to improve the speed an INDEX & MATCH function
which is looking up approx. 12,000 unique values in a 12,000 row list.
I can't use VLOOKUP as I need it to be case sensitive.

The Data
=======
Sheet1
Column A: Unique identifier
Sheet2
Column A: Unique identifier (to be matched to sheet 1 column A)
Column B: Data to return to sheet 1 based on match of unique
identifiers

Formula (in Sheet 1 cell B1):
{=INDEX(Sheet2!$B$1:$B$12000,MATCH(TRUE,EXACT(Sheet1!$A1,Sheet2!$A$1:$A$12000),0))}

The data to look up is integer. The unique identifiers are text
strings, alpha characters only.
I have sorted all the data to be in alphabetic order.

The Problem
=========
This takes 30+ minutes to run.

Idea to Solve
==========
To keep the topic going I'm going to suggest one idea to solve this but
I need to know how to implement it. If this idea is weak then please
suggest other ideas.

It seems that it would be much faster to trim the lookup down to just
search in the range where unique identifier in sheet2 starts with first
letter of the unique identifier in sheet1. Example:
For the letter A:
{=INDEX(Sheet2!$B$1:$B$500,MATCH(TRUE,EXACT(Sheet1!$A1,Sheet2!$A$1:$A$500),0))}
For the letter B:
{=INDEX(Sheet2!$B$501:$B$1001,MATCH(TRUE,EXACT(Sheet1!$A1,Sheet2!$A$501:$A$1001),0))}

if this is a good solution I need help (a) getting the lookup functions
to dynamically set their lookup ranges by alphabet character.

Any help would be appreciated. In your responses please assume that I
know nothing, if you're not already doing so. Thanks

 

[Tom Hutchins]

Instead of an array formula, try this SUMPRODUCT formula in B! and copy down:

=SUMPRODUCT(--(EXACT(Sheet2!A$1:A$12000,Sheet1!A1)),Sheet2!B$1:B$12000)

Because of the EXACT function, it is case sensitive.

Hope this helps,

Hutch