Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
quick double-criteria lookup?
[This may be a duplicate post... I tried continuing the original
thread, but it's not showing up so I'm reposting for more help!] ____ I have a table with 3 columns (employee's last name, first name, and ID). Last names can be duplicated Smith John td232 Smith Mary xk543 Smith Steve aa213 Can someone suggest a formula that I can look up based on the last name and first name to return the ID? (I know I can create a new column that would merge the last name and firstname to create a "lookup" key, but I'm trying to see if there's a way to do the lookup with out using this technique.) John McGimpsey suggested an array-entered formula that does work: =INDEX(C1:C200,MATCH(J1&K1,A1:A200&B1:B200,FALSE)) Although this does work, it is very slow because the spreadsheet recalculates constantly which takes a few seconds each time... yielding the worksheet pretty unusable with the constant pausing. My lookup table is about 20000 lines. Are they any other suggestions for doing a formula-based double- criteria lookup that would use less processor time? Thanks! John |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
quick double-criteria lookup?
Are they any other suggestions for doing a formula-based double-
criteria lookup that would use less processor time? Basically, no. (unless you want to use a column of helper formulas) If you need to make it more efficient you should put the names in a single column then sort you table in ascending order based on the name. A simple VLOOKUP formula can then be used *and* when the table is sorted the formula can be as much as 50% faster. Biff "robotman" wrote in message oups.com... [This may be a duplicate post... I tried continuing the original thread, but it's not showing up so I'm reposting for more help!] ____ I have a table with 3 columns (employee's last name, first name, and ID). Last names can be duplicated Smith John td232 Smith Mary xk543 Smith Steve aa213 Can someone suggest a formula that I can look up based on the last name and first name to return the ID? (I know I can create a new column that would merge the last name and firstname to create a "lookup" key, but I'm trying to see if there's a way to do the lookup with out using this technique.) John McGimpsey suggested an array-entered formula that does work: =INDEX(C1:C200,MATCH(J1&K1,A1:A200&B1:B200,FALSE)) Although this does work, it is very slow because the spreadsheet recalculates constantly which takes a few seconds each time... yielding the worksheet pretty unusable with the constant pausing. My lookup table is about 20000 lines. Are they any other suggestions for doing a formula-based double- criteria lookup that would use less processor time? Thanks! John |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
quick double-criteria lookup?
OK... I added a column of helper formulas and used VLOOKUP.
Processing time is now negligable. With an unsorted list, it takes about 0.5 seconds, with a sorted list, it's almost instantaneous. Thanks for your help! John |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
quick double-criteria lookup?
You're welcome. Thanks for the feedback!
Biff "robotman" wrote in message ups.com... OK... I added a column of helper formulas and used VLOOKUP. Processing time is now negligable. With an unsorted list, it takes about 0.5 seconds, with a sorted list, it's almost instantaneous. Thanks for your help! John |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Variable Lookup/Double Lookup | Excel Worksheet Functions | |||
countif with double criteria | Excel Discussion (Misc queries) | |||
Double Criteria | Excel Worksheet Functions | |||
QUICK HELP - Retrieve value based on two column criteria | Excel Discussion (Misc queries) | |||
Vlookup - double criteria | Excel Worksheet Functions |