Using INDEX AND MATCH, help?

sloppy

New Member
Joined
Apr 2, 2011
Messages
8
<table border="0" cellpadding="0" cellspacing="0" width="629"><col style="width: 65pt;" width="87"> <col style="width: 92pt;" width="122"> <col style="width: 46pt;" width="61"> <col style="width: 59pt;" width="79"> <col style="width: 68pt;" width="91"> <col style="width: 50pt;" width="66"> <col style="width: 48pt;" width="64"> <col style="width: 44pt;" width="59"> <tbody><tr style="height: 15.75pt;" height="21"> <td colspan="2" class="xl76" style="height: 15.75pt; width: 157pt;" width="209" height="21">Sale Price per Apparel and Cost per apparel are two different tables and are separated. I need to find a formula that will help me find the unit cost(cost per apparel) by using details from both details. An example would be i need to find the unit cost of a GUCCI CREW SHIRT. The answer is $19.74. I used multiple index and match functions to try to find this but its not working out. Any ideas? Should i include Vlookup?






Sale price per apparel
</td> <td style="width: 46pt;" width="61">
</td> <td style="width: 59pt;" width="79">
</td> <td style="width: 68pt;" width="91">
</td> <td colspan="3" class="xl76" style="width: 142pt;" width="189">


















Cost per apparel
</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl69" style="height: 15.75pt; border-top: medium none;" height="21">Merchandise</td> <td class="xl71" style="border-top: medium none; border-left: medium none;"> T</td> <td class="xl71" style="border-left: medium none;">CREW</td> <td class="xl72" style="border-left: medium none;">ZIP_UP</td> <td>
</td> <td class="xl68" style="border-top: medium none;">T</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">CREW</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">ZIP_UP</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl70" style="height: 15pt;" height="20">
PKT SQ
</td> <td class="xl74" style="border-top: medium none;"> 36.88 </td> <td class="xl74" style="border-top: medium none; border-left: medium none;"> 43.81 </td> <td class="xl74" style="border-top: medium none; border-left: medium none;"> 30.21 </td> <td>
</td> <td class="xl66">12.65</td> <td class="xl66" style="border-left: medium none;">15.63</td> <td class="xl66" style="border-left: medium none;">11.87</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl70" style="height: 15pt;" height="20">
TRUMPET
</td> <td class="xl74" style="border-top: medium none;"> 30.94 </td> <td class="xl74" style="border-top: medium none; border-left: medium none;"> 39.51 </td> <td class="xl74" style="border-top: medium none; border-left: medium none;"> 40.80 </td> <td>
</td> <td class="xl66">14.86</td> <td class="xl66" style="border-left: medium none;">15.47</td> <td class="xl66" style="border-left: medium none;">15.32</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl70" style="height: 15pt;" height="20">
GUCCI
</td> <td class="xl74" style="border-top: medium none;"> 43.66 </td> <td class="xl74" style="border-top: medium none; border-left: medium none;"> 57.52 </td> <td class="xl74" style="border-top: medium none; border-left: medium none;"> 36.96 </td> <td>
</td> <td class="xl66">18.74</td> <td class="xl66" style="border-left: medium none;">19.74</td> <td class="xl66" style="border-left: medium none;">15.54</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl70" style="height: 15pt;" height="20">
KINEY
</td> <td class="xl74" style="border-top: medium none;"> 28.47 </td> <td class="xl74" style="border-top: medium none; border-left: medium none;"> 43.69 </td> <td class="xl74" style="border-top: medium none; border-left: medium none;"> 31.67 </td> <td>
</td> <td class="xl66">12.65</td> <td class="xl66" style="border-left: medium none;">16.21</td> <td class="xl66" style="border-left: medium none;">12.76</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl70" style="height: 15pt;" height="20">
DOPE N°5
</td> <td class="xl74" style="border-top: medium none;"> 22.97 </td> <td class="xl74" style="border-top: medium none; border-left: medium none;"> 29.18 </td> <td class="xl74" style="border-top: medium none; border-left: medium none;"> 26.51 </td> <td>
</td> <td class="xl66">10.86</td> <td class="xl66" style="border-left: medium none;">9.78</td> <td class="xl66" style="border-left: medium none;">9.43</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl70" style="height: 15pt;" height="20">
SOHO
</td> <td class="xl74" style="border-top: medium none;"> 48.49 </td> <td class="xl74" style="border-top: medium none; border-left: medium none;"> 36.46 </td> <td class="xl74" style="border-top: medium none; border-left: medium none;"> 32.22 </td> <td>
</td> <td class="xl66">16.43</td> <td class="xl66" style="border-left: medium none;">12.54</td> <td class="xl66" style="border-left: medium none;">13.64</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl70" style="height: 15.75pt;" height="21">
VELO
</td> <td class="xl74" style="border-top: medium none;"> 33.90 </td> <td class="xl74" style="border-top: medium none; border-left: medium none;"> 36.06 </td> <td class="xl74" style="border-top: medium none; border-left: medium none;"> 42.26 </td> <td>
</td> <td class="xl67">12.78</td> <td class="xl67" style="border-left: medium none;">16.54</td> <td class="xl67" style="border-left: medium none;">18.74
</td> </tr> </tbody></table>
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
<TABLE cellSpacing=0 cellPadding=0 width=629 border=0><COLGROUP><COL style="WIDTH: 65pt" width=87><COL style="WIDTH: 92pt" width=122><COL style="WIDTH: 46pt" width=61><COL style="WIDTH: 59pt" width=79><COL style="WIDTH: 68pt" width=91><COL style="WIDTH: 50pt" width=66><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 44pt" width=59><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD class=xl76 style="WIDTH: 157pt; HEIGHT: 15.75pt" width=209 colSpan=2 height=21>Sale Price per Apparel and Cost per apparel are two different tables and are separated. I need to find a formula that will help me find the unit cost(cost per apparel) by using details from both details. An example would be i need to find the unit cost of a GUCCI CREW SHIRT. The answer is $19.74. I used multiple index and match functions to try to find this but its not working out. Any ideas? Should i include Vlookup?






Sale price per apparel
</TD><TD style="WIDTH: 46pt" width=61>

</TD><TD style="WIDTH: 59pt" width=79>

</TD><TD style="WIDTH: 68pt" width=91>

</TD><TD class=xl76 style="WIDTH: 142pt" width=189 colSpan=3>


















Cost per apparel
</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl69 style="BORDER-TOP: medium none; HEIGHT: 15.75pt" height=21>Merchandise</TD><TD class=xl71 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">T</TD><TD class=xl71 style="BORDER-LEFT: medium none">CREW</TD><TD class=xl72 style="BORDER-LEFT: medium none">ZIP_UP</TD><TD>

</TD><TD class=xl68 style="BORDER-TOP: medium none">T</TD><TD class=xl68 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">CREW</TD><TD class=xl68 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">ZIP_UP</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl70 style="HEIGHT: 15pt" height=20>
PKT SQ
</TD><TD class=xl74 style="BORDER-TOP: medium none">36.88 </TD><TD class=xl74 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">43.81 </TD><TD class=xl74 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">30.21 </TD><TD>

</TD><TD class=xl66>12.65</TD><TD class=xl66 style="BORDER-LEFT: medium none">15.63</TD><TD class=xl66 style="BORDER-LEFT: medium none">11.87</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl70 style="HEIGHT: 15pt" height=20>
TRUMPET
</TD><TD class=xl74 style="BORDER-TOP: medium none">30.94 </TD><TD class=xl74 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">39.51 </TD><TD class=xl74 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">40.80 </TD><TD>

</TD><TD class=xl66>14.86</TD><TD class=xl66 style="BORDER-LEFT: medium none">15.47</TD><TD class=xl66 style="BORDER-LEFT: medium none">15.32</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl70 style="HEIGHT: 15pt" height=20>
GUCCI
</TD><TD class=xl74 style="BORDER-TOP: medium none">43.66 </TD><TD class=xl74 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">57.52 </TD><TD class=xl74 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">36.96 </TD><TD>

</TD><TD class=xl66>18.74</TD><TD class=xl66 style="BORDER-LEFT: medium none">19.74</TD><TD class=xl66 style="BORDER-LEFT: medium none">15.54</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl70 style="HEIGHT: 15pt" height=20>
KINEY
</TD><TD class=xl74 style="BORDER-TOP: medium none">28.47 </TD><TD class=xl74 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">43.69 </TD><TD class=xl74 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">31.67 </TD><TD>

</TD><TD class=xl66>12.65</TD><TD class=xl66 style="BORDER-LEFT: medium none">16.21</TD><TD class=xl66 style="BORDER-LEFT: medium none">12.76</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl70 style="HEIGHT: 15pt" height=20>
DOPE N°5
</TD><TD class=xl74 style="BORDER-TOP: medium none">22.97 </TD><TD class=xl74 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">29.18 </TD><TD class=xl74 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">26.51 </TD><TD>

</TD><TD class=xl66>10.86</TD><TD class=xl66 style="BORDER-LEFT: medium none">9.78</TD><TD class=xl66 style="BORDER-LEFT: medium none">9.43</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl70 style="HEIGHT: 15pt" height=20>
SOHO
</TD><TD class=xl74 style="BORDER-TOP: medium none">48.49 </TD><TD class=xl74 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">36.46 </TD><TD class=xl74 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">32.22 </TD><TD>

</TD><TD class=xl66>16.43</TD><TD class=xl66 style="BORDER-LEFT: medium none">12.54</TD><TD class=xl66 style="BORDER-LEFT: medium none">13.64</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl70 style="HEIGHT: 15.75pt" height=21>
VELO
</TD><TD class=xl74 style="BORDER-TOP: medium none">33.90 </TD><TD class=xl74 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">36.06 </TD><TD class=xl74 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">42.26 </TD><TD>

</TD><TD class=xl67>12.78</TD><TD class=xl67 style="BORDER-LEFT: medium none">16.54</TD><TD class=xl67 style="BORDER-LEFT: medium none">18.74

</TD></TR></TBODY></TABLE>
Let's assume your table is in the range A1:H8 with column E being an empty column separating the 2 parts of the table.

Use cells to hold the lookup criteria:
  • A15 = GUCCI
  • B15 = Crew
Then:

=INDEX(F2:H8,MATCH(A15,A2:A8,0),MATCH(B15,F1:H1,0))

If you wanted, you could also use a cell to enter which table to use, either Cost or Sale.
 
Upvote 0

Forum statistics

Threads
1,224,600
Messages
6,179,836
Members
452,947
Latest member
Gerry_F

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top