Saved from a previous post:

If you want exact matches for just two columns (and return a value from a
third), you could use:

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

(all in 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 only use the whole column in xl2007.

This returns the value in othersheet column C when column A and B (of
othersheet) match A2 and B2 of the sheet with the formula.

And you can add more conditions by just adding more stuff to that product
portion of the formula:

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

Kay wrote:

Hello all,

I have a customer who gets downloads of tons of live data into excel. Each
day the data is downloaded, it must be compared to another database. For
unexplained reasons other than size limitations, the data cannot be combined.
Now, both databases include among others three fields..first name, last
name, cost center.
There could be several John Smiths each with a different cost center, but
there could be multiple John Smiths in the same cost center. There is no
other unique identifier for the record. They need to see if there is a
matching record in the second database. A vlookup does not seem to work
since you can only base the lookup on one value. I beleive you have to
lookup on all three fields to really check for a match. What am I missing.
Is there any way to do this?

--

Dave Peterson