quick double-criteria lookup?

[robotman]

[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

 

[T. Valko]

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

[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

 

[robotman]

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

 

[T. Valko]

You're welcome. Thanks for the feedback!

Biff