Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to merge 2 Excel files that need to match on 2 different colum
I have 2 worksheets that are set up with similiar fields. The difference is
one worksheet is from one year and the other worksheet is another year. I would normally use a vlookup function to join the 2 files but I need to match these 2 worksheets using 2 columns (e.g. car make and car dealer). Thanks. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to merge 2 Excel files that need to match on 2 different colum
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)) graymore393 wrote: I have 2 worksheets that are set up with similiar fields. The difference is one worksheet is from one year and the other worksheet is another year. I would normally use a vlookup function to join the 2 files but I need to match these 2 worksheets using 2 columns (e.g. car make and car dealer). Thanks. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find match between 2 columns and then record data that is in colum | Excel Discussion (Misc queries) | |||
Look up data in colum a and find match in colum b | Excel Discussion (Misc queries) | |||
Check data on colum A and find match on colum b | Excel Discussion (Misc queries) | |||
Look up data in colum a and find match in colum b | Excel Discussion (Misc queries) | |||
How do I merge two files in Excel? | Excel Discussion (Misc queries) |