Lookup on more than one variable in 2003

[Kay]

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?

 

[Niek Otten]

Concatenate both the 3 search keys and the keys in the table (in an extra column) and do a lookup on those

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| 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?

 

[Niek Otten]

In theory you could get confusion with names like

John Slow
Johns Low

Not a good example, but you get the point, I hope.

You could concatenate separators into the combination, like

=A1&"|"&B1&"|"&C1

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Concatenate both the 3 search keys and the keys in the table (in an extra column) and do a lookup on those
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
|| 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]

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))