Lookup Help - very tricky

[Jason Beck]

This is tricky, and I would love for some insight.

On one sheet I have a list of my employees and if they have taken a
particular course.

What I need to do is cross reference this data with another sheet that
has the date the person completed the course.

The problem is that by using the Vlookup function, it only allows for
me to lookup the name or the course. I need to look up 2 unique values
(name and course), and return 1 (completion date).

Anyone have any ideas?

(e-mail address removed)

 

[Dave Peterson]

I like this syntax:

=index(othersheet!$c$1:$c$10,
match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet!$b$1:$b$100),0))

(one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.