Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup on more than one variable in 2003
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup on more than one variable in 2003
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 "Kay" wrote in message ... | 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup on more than one variable in 2003
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 "Niek Otten" wrote in message ... | 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 | | "Kay" wrote in message ... || 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? | | |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup on more than one variable in 2003
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
can't lookup a value to the left of my variable | Excel Worksheet Functions | |||
3 variable lookup | Excel Worksheet Functions | |||
Two Variable Lookup with approximate ranges | Excel Worksheet Functions | |||
Variable Lookup/Double Lookup | Excel Worksheet Functions | |||
How can I set a variable for which column to lookup? | Excel Discussion (Misc queries) |