Hi!

I put the function in wks#2 column C1
=index([wks#1]C1:C100,match(A1&B1,[wk#1]A1:A100&B1:B100,0))

I got a #value!

Type this formula:

=INDEX(Sheet1!C1:C100,MATCH(A1&B1,Sheet1!A1:A100&S heet1!B1:B100,0))

Replace Sheet1 with your actual sheet name.

Now, when you're done typing the formula INSTEAD of hitting the enter key
hold down the CTRL key and the SHIFT key then hit ENTER. When done properly
Excel will place squiggly braces { } around the formula. The braces denote
an array formula. You cannot just type the braces, you MUST use the key
combo of CTRL,SHIFT,ENTER.

Biff

"SL" wrote in message
...
Biff,

Thank you for your reply. I tried the index but I got #value! error. In
your example:
lookup table:
column A = last name
Column B = first name
Column C = Student Id

then you used D1 = williams
E1 = Venus
Here is where I was confused. Venus williams say would be the student I
am
looking up to find the student id. So my wks#2 has a list of last name
and
first name say :
Column A = Last name (ex:williams)
Column B = first name (ex:Venus)

I put the function in wks#2 column C1
=index([wks#1]C1:C100,match(A1&B1,[wk#1]A1:A100&B1:B100,0))

I got a #value!

What went wrong??? Am I messing up the function?

Thanks for your help Biff
SL

"Biff" wrote:

Hi!

Maybe this:

=SUMPRODUCT(--(A1:A100="last_name"),--(B1:B100="first_name"),C1:C100)

OR....

Redo your lookup table so that the first column includes both
lastname,firstname

OR....

Assume lookup table is such:

Column A = lastname
Column B = firstname
Column C = student number

D1 = Williams
E1 = Venus

=INDEX(C1:C100,MATCH(D1&E1,A1:A100&B1:B100,0))

Array entered using the key combo of CTRL,SHIFT,ENTER.

Biff

"SL" wrote in message
...
I am using two spreadsheets. Both spreadsheets have last name and first
names of students from our school. I am trying to lookup the student
numbers
from one spreadsheet and populate to the other. I can do it just fine
except
when there are duplicate last names. Does anyone know how to lookup
more
than just one column on duplicate last names. Example, Williams (last
name)
tons of them, I need Excel to look at Williams and first name (Jane)
before
returning the student number.

Thanks. SL