Forum Discussion
How do I copy and paste excel rows based on a common cell value (common key) ?
Ok I inherited an Excel document that has a bunch of mismatched records(17k). I'm trying to find a way to append rows from a common value in the far left column (Col D) to Col L and all the rows associated with to column X. Do I use VLOOKUP or is there a rule I would have to do ? This would save me a lot of time.
thanks!
=INDEX($X$2:$AC$10,MATCH($L17,$X$2:$X$10,0),COLUMN(A$1))
=VLOOKUP($L17,$X$2:$AC$10,COLUMN(A$1),FALSE)
Does the small sample represent what you want to do with a large database? Either INDEX and MATCH or VLOOKUP can be entered in cell M17 and filled across range M17:R25 to append the data. Both formulas work in Excel 2013 and in all more recent versions of Excel.
=FILTER($X$2:$AC$10,$X$2:$X$10=L17)
If you have access to the FILTER function you can use this formula. In the example the formula is in cell M17 and filled down to cell M25.
=INDEX($X$2:$AC$10,MATCH($L17,$X$2:$X$10,0),COLUMN(A$1))
=VLOOKUP($L17,$X$2:$AC$10,COLUMN(A$1),FALSE)
Does the small sample represent what you want to do with a large database? Either INDEX and MATCH or VLOOKUP can be entered in cell M17 and filled across range M17:R25 to append the data. Both formulas work in Excel 2013 and in all more recent versions of Excel.
=FILTER($X$2:$AC$10,$X$2:$X$10=L17)
If you have access to the FILTER function you can use this formula. In the example the formula is in cell M17 and filled down to cell M25.
- thank you so much! you are a life saver!!!
