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

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

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

In article .com,
" wrote:

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

That did the trick. Thanks!

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

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

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/microsof...ic.excel.misc), or via the Google
archive
(http://groups.google.co.uk/group/mic...l.misc/topics).
--
David Biddulph

"robotman" wrote in message
ps.com...
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