index match

[bluepenink]

hello

having some issues with my index match formula

for some reaching it's not looking up by what i want

=INDEX(SortData,MATCH($C20,Calculations!$AA$10:$AA$955,0),6)
where sort data = =Calculations!$D$10:$AA$955

do basically its going in the right column 6, but its not matching by the store #, i want it to FIND the revenue for cell $C20

the formula that i have, its jsut simply listing in order...its not matching by C20 the revenue....can someone pls help!

 

[Sal Paradise]

• What column is the revenue in?
• What column is the store number in?
• Are there any duplicate revenues?

 

[bluepenink]

hello buddy

the revenue i want is in column I

the store # is located in column K that relates to column I

regarding duplicates, column D, S and AA contain the same store numbers as well....

basically i have sorted my data by
a) week
b) month
c) YTD

for this particular example, my main data is sorted by YTD, so for the YTD store #, i want to know it's weekly revenue, which is stored in column I

any ideas? thxs bud

 

[Sal Paradise]

Your request is still unclear.

What does your sheet look like?

Like this?

<b>Excel 2003</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 /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>C</th><th>I</th><th>AA</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style=";">Revenue</td><td style=";">Store #</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"> $ 14,710.00 </td><td style="text-align: right;;">190</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"> $ 11,940.00 </td><td style="text-align: right;;">941</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"> $ 13,230.00 </td><td style="text-align: right;;">991</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"> $ 15,040.00 </td><td style="text-align: right;;">422</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"> $ 23,130.00 </td><td style="text-align: right;;">369</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"> $ 17,790.00 </td><td style="text-align: right;;">281</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"> $ 28,560.00 </td><td style="text-align: right;;">88</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"> $ 27,190.00 </td><td style="text-align: right;;">439</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"> $ 14,650.00 </td><td style="text-align: right;;">660</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;"></td><td style="text-align: right;;"> $ 16,040.00 </td><td style="text-align: right;;">840</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;"></td><td style="text-align: right;;"> $ 12,380.00 </td><td style="text-align: right;;">43</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;"></td><td style="text-align: right;;"> $ 27,360.00 </td><td style="text-align: right;;">102</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;"></td><td style="text-align: right;;"> $ 20,210.00 </td><td style="text-align: right;;">500</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;"></td><td style="text-align: right;;"> $ 16,610.00 </td><td style="text-align: right;;">717</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;"></td><td style="text-align: right;;"> $ 13,280.00 </td><td style="text-align: right;;">681</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;"></td><td style="text-align: right;;"> $ 21,370.00 </td><td style="text-align: right;;">842</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;"></td><td style="text-align: right;;"> $ 14,230.00 </td><td style="text-align: right;;">480</td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style=";">Store #</td><td style="text-align: right;;"> $ 13,550.00 </td><td style="text-align: right;;">304</td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;;">123</td><td style="text-align: right;;"> $ 25,390.00 </td><td style="text-align: right;;">123</td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: right;;"></td><td style="text-align: right;;"> $ 16,950.00 </td><td style="text-align: right;;">464</td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: right;;"></td><td style="text-align: right;;"> $ 20,160.00 </td><td style="text-align: right;;">127</td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="text-align: right;;"></td><td style="text-align: right;;"> $ 19,570.00 </td><td style="text-align: right;;">203</td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style="text-align: right;;"></td><td style="text-align: right;;"> $ 22,320.00 </td><td style="text-align: right;;">980</td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style="text-align: right;;"></td><td style="text-align: right;;"> $ 17,770.00 </td><td style="text-align: right;;">256</td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style="text-align: right;;"></td><td style="text-align: right;;"> $ 21,120.00 </td><td style="text-align: right;;">124</td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style="text-align: right;;"></td><td style="text-align: right;;"> $ 21,910.00 </td><td style="text-align: right;;">706</td></tr><tr ><td style="color: #161120;text-align: center;">28</td><td style="text-align: right;;"></td><td style="text-align: right;;"> $ 14,490.00 </td><td style="text-align: right;;">81</td></tr><tr ><td style="color: #161120;text-align: center;">29</td><td style="text-align: right;;"></td><td style="text-align: right;;"> $ 21,450.00 </td><td style="text-align: right;;">581</td></tr><tr ><td style="color: #161120;text-align: center;">30</td><td style="text-align: right;;"></td><td style="text-align: right;;"> $ 16,790.00 </td><td style="text-align: right;;">149</td></tr><tr ><td style="color: #161120;text-align: center;">31</td><td style="text-align: right;;"></td><td style="text-align: right;;"> $ 16,980.00 </td><td style="text-align: right;;">958</td></tr><tr ><td style="color: #161120;text-align: center;">32</td><td style="text-align: right;;"></td><td style="text-align: right;;"> $ 15,050.00 </td><td style="text-align: right;;">83</td></tr><tr ><td style="color: #161120;text-align: center;">33</td><td style="text-align: right;;"></td><td style="text-align: right;;"> $ 29,040.00 </td><td style="text-align: right;;">958</td></tr><tr ><td style="color: #161120;text-align: center;">34</td><td style="text-align: right;;"></td><td style="text-align: right;;"> $ 16,250.00 </td><td style="text-align: right;;">59</td></tr><tr ><td style="color: #161120;text-align: center;">35</td><td style="text-align: right;;"></td><td style="text-align: right;;"> $ 17,790.00 </td><td style="text-align: right;;">673</td></tr><tr ><td style="color: #161120;text-align: center;">36</td><td style="text-align: right;;"></td><td style="text-align: right;;"> $ 27,040.00 </td><td style="text-align: right;;">515</td></tr><tr ><td style="color: #161120;text-align: center;">37</td><td style="text-align: right;;"></td><td style="text-align: right;;"> $ 10,600.00 </td><td style="text-align: right;;">416</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">Sheet3</p><br /><br />

If so, your formula should work.

What answer are you getting?

What answer are you expecting?