please HELP! Need to match up/lookup data in different columns in my spreadsheet!

[jenniferical]

Hello,

I really need some help here, and I was hoping that someone might be able to answer my formula question please (I've been trying to figure this out for 4 hours, and I know one of you experts will have it solved in 2 minutes )

Ok, so I have a spreadsheet with this layout (see below). In column A, I have an order code for each product, in column B and C, I have empty fields for the IPN number, and the sale price for each item. Columns D, E, and F, have been pasted into this spreadsheet from another spreadsheet, and columns D, E, and F have about 10 times more products than columns A and B (columns A and B are selected products that we will be featuring on our website).

What I need to do, is find a formula that will look in column D, to see if the order code there matches up with column A. If it does, I need it to take the data from the next two cells for IPN number and Sell Price, and have it appear in columns B and C, respectively. So, if, for example, the order code in D500 is the same as the order code in A2, it will take the IPN from E500 and the cell price in F500 and place that into B2 and C2.

I really hope I'm making sense here, and I really hope someone can help me! Please and Thank-You in advance if anyone can help out

<table width="768" border="0" cellpadding="0" cellspacing="0"><col style="width: 17pt;" width="22"> <col style="width: 116pt;" width="155"> <col style="width: 116pt;" width="155"> <col style="width: 112pt;" width="149"> <col style="width: 48pt;" width="64"> <col style="width: 119pt;" width="159"> <col style="width: 48pt;" width="64"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt; width: 17pt;" width="22" height="17"> </td> <td class="xl26" style="border-left: medium none; width: 116pt;" width="155">A </td> <td class="xl26" style="border-left: medium none; width: 116pt;" width="155">B</td> <td class="xl25" style="border-left: medium none; width: 112pt;" width="149">C</td> <td class="xl25" style="border-left: medium none; width: 48pt;" width="64">D </td> <td class="xl25" style="border-left: medium none; width: 119pt;" width="159">E</td> <td class="xl25" style="border-left: medium none; width: 48pt;" width="64">F </td> </tr> <tr style="height: 22.5pt;" height="30"> <td class="xl27" style="height: 22.5pt; border-top: medium none;" align="right" height="30">1</td> <td class="xl28" style="border-top: medium none; border-left: medium none;">Order Code</td> <td class="xl29" style="border-top: medium none; border-left: medium none;">IPN</td> <td class="xl29" style="border-top: medium none; border-left: medium none;">Price_Vat</td> <td class="xl30" style="border-top: medium none; border-left: medium none; width: 48pt;" width="64">Order Code</td> <td class="xl31" style="border-top: medium none; border-left: medium none;">IPN</td> <td class="xl32" style="border-top: medium none; border-left: medium none;">----- High -----</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27" style="height: 12.75pt; border-top: medium none;" align="right" height="17">2</td> <td class="xl33" style="border-top: medium none; border-left: medium none; width: 116pt;" width="155">HHD5205</td> <td class="xl34" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl27" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl27" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl27" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl31" style="border-top: medium none; border-left: medium none;">Sell</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27" style="height: 12.75pt; border-top: medium none;" align="right" height="17">3</td> <td class="xl35" style="border-top: medium none; border-left: medium none;">DB01-6</td> <td class="xl35" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl27" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl27" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl36" style="border-top: medium none; border-left: medium none;">DEPARTME</td> <td class="xl27" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27" style="height: 12.75pt; border-top: medium none;" align="right" height="17">4</td> <td class="xl35" style="border-top: medium none; border-left: medium none;">DB02-6</td> <td class="xl35" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl27" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl27" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl37" style="border-top: medium none; border-left: medium none;">187015815</td> <td class="xl38" style="border-top: medium none; border-left: medium none;">3.99</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27" style="height: 12.75pt; border-top: medium none;" align="right" height="17">5</td> <td class="xl35" style="border-top: medium none; border-left: medium none;">DB03-6</td> <td class="xl35" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl27" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl27" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl36" style="border-top: medium none; border-left: medium none;">F&D</td> <td class="xl27" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27" style="height: 12.75pt; border-top: medium none;" align="right" height="17">6</td> <td class="xl35" style="border-top: medium none; border-left: medium none;">DB04-6</td> <td class="xl35" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl27" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl39" style="border-top: medium none; border-left: medium none;">270008</td> <td class="xl37" style="border-top: medium none; border-left: medium none;">141016960</td> <td class="xl38" style="border-top: medium none; border-left: medium none;">1.40</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27" style="height: 12.75pt; border-top: medium none;" align="right" height="17">7</td> <td class="xl35" style="border-top: medium none; border-left: medium none;">DB05-6</td> <td class="xl35" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl27" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl39" style="border-top: medium none; border-left: medium none;">270001</td> <td class="xl37" style="border-top: medium none; border-left: medium none;">141016952</td> <td class="xl38" style="border-top: medium none; border-left: medium none;">1.40</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27" style="height: 12.75pt; border-top: medium none;" align="right" height="17">8</td> <td class="xl33" style="border-top: medium none; border-left: medium none; width: 116pt;" width="155">HHD5730</td> <td class="xl33" style="border-top: medium none; border-left: medium none; width: 116pt;" width="155"> </td> <td class="xl27" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl39" style="border-top: medium none; border-left: medium none;">270002</td> <td class="xl37" style="border-top: medium none; border-left: medium none;">141016954</td> <td class="xl38" style="border-top: medium none; border-left: medium none;">1.40</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27" style="height: 12.75pt; border-top: medium none;" align="right" height="17">9</td> <td class="xl35" style="border-top: medium none; border-left: medium none;">M504038BL</td> <td class="xl35" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl27" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl39" style="border-top: medium none; border-left: medium none;">270007</td> <td class="xl37" style="border-top: medium none; border-left: medium none;">141016956</td> <td class="xl38" style="border-top: medium none; border-left: medium none;">1.40</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27" style="height: 12.75pt; border-top: medium none;" align="right" height="17">10</td> <td class="xl35" style="border-top: medium none; border-left: medium none;">M504045BL 15</td> <td class="xl35" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl27" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl39" style="border-top: medium none; border-left: medium none;">270015</td> <td class="xl37" style="border-top: medium none; border-left: medium none;">141016958</td> <td class="xl38" style="border-top: medium none; border-left: medium none;">1.30</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27" style="height: 12.75pt; border-top: medium none;" align="right" height="17">11</td> <td class="xl35" style="border-top: medium none; border-left: medium none;">M504053BL 15</td> <td class="xl35" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl27" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl27" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl36" style="border-top: medium none; border-left: medium none;">FURNITUR</td> <td class="xl27" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27" style="height: 12.75pt; border-top: medium none;" align="right" height="17">12</td> <td class="xl35" style="border-top: medium none; border-left: medium none;">M504061BL 15</td> <td class="xl35" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl27" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl40" style="border-top: medium none; border-left: medium none;">BA0500058</td> <td class="xl37" style="border-top: medium none; border-left: medium none;">187013041</td> <td class="xl38" style="border-top: medium none; border-left: medium none;">6.99</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27" style="height: 12.75pt; border-top: medium none;" align="right" height="17">13</td> <td class="xl35" style="border-top: medium none; border-left: medium none;">M504069BL 15</td> <td class="xl35" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl27" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl27" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl36" style="border-top: medium none; border-left: medium none;">FURNITUR</td> <td class="xl27" style="border-top: medium none; border-left: medium none;"> </td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27" style="height: 12.75pt; border-top: medium none;" align="right" height="17">14</td> <td class="xl35" style="border-top: medium none; border-left: medium none;">M504077BL 15</td> <td class="xl35" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl27" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl39" style="border-top: medium none; border-left: medium none;">27464</td> <td class="xl37" style="border-top: medium none; border-left: medium none;">114009995</td> <td class="xl41" style="border-top: medium none; border-left: medium none;">24.99</td> </tr> </tbody></table>

 

[mvptomlinson]

Assuming your order codes in column D contain no duplicates, you should be able to use a simple VLOOKUP.

In B2: =VLOOKUP(A2,$D$2:$F$1000,2,0)
In C2: =VLOOKUP(A2,$D$2:$F$1000,3,0)

Change 1000 to your last row of actual data in columns D:F.

 

[jenniferical]

Hello, I am working with Excel 2003. Also, here is a link to my file:

http://www.mediafire.com/?vszbqbn3d6l2655

Thanks

 

[jenniferical]

Hi, I tried that formula but it came up as #N/A

 

[mvptomlinson]

You have to adjust the formula I gave you to suit your range. I didn't have your workbook before, so it was just a baseline.

In C3: =VLOOKUP($B3,$E$3:$G$31702,2,0)
In D3: =VLOOKUP($B3,$E$3:$G$31702,3,0)

You can then fill those formulas down columns C & D. Wherever you see "#N/A" errors means the function did not find an exact match in column E.. and I mean EXACT.. no extra spaces, periods, etc.

If you know some of the values in column B won't be found in column E, you can hide the N/A errors using

=IF(ISERROR(VLOOKUP($B3,$E$3:$G$31702,2,0)),"",VLOOKUP($B3,$E$3:$G$31702,2,0))

and

=IF(ISERROR(VLOOKUP($B3,$E$3:$G$31702,3,0)),"",VLOOKUP($B3,$E$3:$G$31702,3,0))

 

[jenniferical]

THANK YOU SO MUCH!!!! You are a complete genius, problem solved! Have a fantastic day