Lookup based on two fields without merging?

[robotman]

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.

Since the ID contains letters, a SUMPRODUCT doesn't work (or I can't
figure out the syntax).

Any ideas?!

Thanks!

John

 

[JE McGimpsey]

One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=INDEX(C1:C200,MATCH(J1&K1,A1:A200&B1:B200,FALSE))

 

[robotman]

That did the trick. Thanks!

 

[robotman]

After playing with this some more, it does work, but it is very slow
because the spreadsheet recalculates constantly which takes a few
seconds each time. 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]

Can anyone see this thread in the active forum?! ... It's not showing
up in the forum for me even though the time stamp shows it should be.
I reposted, to continue this thread... but if anyone has insight on
why I (or anyone?) can't see this thread, please let me know!

Thanks!

John

 

[David Biddulph]

The Microsoft web interface to the newsgroup is broken.

You can see messages on the newsgroup directly via a news server (such as
news://msnews.microsoft.com/microsoft.public.excel.misc), or via the Google
archive
(http://groups.google.co.uk/group/microsoft.public.excel.misc/topics).