Match Columns and Data

[cdubs34]

So I have two sets of data on separate sheets that I want to match and line up. I subsequently want a column of data that is associated with the data on one sheet to line up as well. For example

Sheet 1 Column E
Code
2345
3455
4458
3054

Sheet 2 Column C
Code
004458
003054
003455
004829
002345

Sheet 2 Column D
Sales
$4859
$8569
$9596
$9529
$8859

I have a workbook with many data points like this so was wondering a quick, easy way to get the Sales Data on Sheet 2 to line up with the matching code from sheet 1. Also, note that the codes on Sheet 2 contain the codes from Sheet 1, but are not exactly the same (ie they have 00 and then the same code). Not sure if this is clear or makes sense. Any help you can provide is appreciated. Thanks

 

[Snailspace]

A quick fix would be to insert a column next the code in Sheet2, copy Col C then use text to columns to remove the leading zeros ( or text to columns on Col E if you don’t need it to stay in the same format)<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Then use a Vlookup in Sheet 1<o></o>

 

[markmzz]

Try this (array formula - use Ctrl+Shift+Enter and not only Enter):

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;">Code</td><td style="text-align: center;;">Sales</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">2345</td><td style="text-align: right;;">$8,859</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">3455</td><td style="text-align: right;;">$9,596</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">4458</td><td style="text-align: right;;">$4,859</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">3054</td><td style="text-align: right;;">$8,569</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F2</th><td style="text-align:left">{=INDEX(<font color="Blue">Sheet2!D$2:D$6,MATCH(<font color="Red">E2,Sheet2!C$2:C$6*1,0</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br /><b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Code</td><td style=";">Sales</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">004458</td><td style="text-align: right;;">$4,859</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">003054</td><td style="text-align: right;;">$8,569</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">003455</td><td style="text-align: right;;">$9,596</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">004829</td><td style="text-align: right;;">$9,529</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">002345</td><td style="text-align: right;;">$8,859</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet2</p><br /><br />
Markmzz

 

[cdubs34]

Thanks very much Snail and Markmzz. I tried both of your solutions however I still can't come up with a resolution. Can I please send one of you my worksheet and see if you can help me out. Thank you very much

 

[markmzz]

Thanks very much Snail and Markmzz. I tried both of your solutions however I still can't come up with a resolution. Can I please send one of you my worksheet and see if you can help me out. Thank you very much

Cdubs34,

Look at your MP.

Markmzz

 

[John Davis]

Thanks very much Snail and Markmzz. I tried both of your solutions however I still can't come up with a resolution. Can I please send one of you my worksheet and see if you can help me out. Thank you very much

See my post to this thread earlier today regarding VLOOKUPNTH. Perhaps that can help.

http://www.mrexcel.com/forum/showthread.php?t=567108

 

[cdubs34]

Hi All,

Thanks for your responses and big thanks to Markmzz who came up with the solution.

=IF(ISERROR(MATCH($E2,Sheet2!$C$4:$C$2457*1,0)),"",INDEX(Sheet2!D$4:D$2457,MATCH($E2,Sheet2!$C$4:$C$2457*1,0)))
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

 

[markmzz]

Hi All,

Thanks for your responses and big thanks to Markmzz who came up with the solution.

=IF(ISERROR(MATCH($E2,Sheet2!$C$4:$C$2457*1,0)),"",INDEX(Sheet2!D$4:D$2457,MATCH($E2,Sheet2!$C$4:$C$2457*1,0)))
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

Thanks for the feedback.

Markmzz