Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Optimizing Index / Match function
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(Shee t1!$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(She et1!$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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Optimizing Index / Match function
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 " wrote: 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(Shee t1!$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(She et1!$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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need help with function using INDEX and MATCH. | Excel Worksheet Functions | |||
Help with Index / Match function | Excel Worksheet Functions | |||
index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
index match function help me | Excel Worksheet Functions | |||
Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions |