Dynamic VLOOKUP within a expanding/contracting column range

[nickpape]

VERSION
Excel 2013 (15.0.4631.1000)

SUMMARY OF ISSUE

Creating a dynamic VLOOKUP within a expanding/contracting column range

BACKSTORY
Within my business, I have an internal reporting suite of which I export monthly sales data. The data details which shops have generated sales. Some months all shops generate revenue, other months, only a handful do. As a result, the exported report can have a small column range or a very wide column range.

WHERE THE CHALLENGE LIES

As the exported report is pre-formatted (designed for printing), no raw data export is possible. Painfully, I've created a VLOOKUP s/sheet to pick up (what I thought to be a static) data range. Creating a separate excel file, One worksheet has the layout of the previously exported report of which I use as a data dump. Another, is the VLOOKUP data fields picking up data from this 'data dump' worksheet. The result, is rows of Shop A as a row, vs. a column.

It is only after many hours, I've realised what I thought to be a static report layout is dynamic, expanding and contracting based on which shops generated sales. I had assumed any shop not generating sales would just a have a £0 value.

WHAT I'VE DONE SO FAR

I am trying to create what is a dynamic VLOOKUP that not only finds the data (based on the exact i.e. FALSE) but double checks the Shop reference is also correct.

Reading more about INDEX and MATCH formulas, is the best way to tackle this challenge? Could anybody recommend a formula to VLOOKUP the 'sport' but then yet have a unique argument limiting to each Shop? I.e. there is a single formula for Shop A, B etc...


<style type="text/css">
table.tableizer-table {
border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif
font-size: 12px;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #ccc;
}
.tableizer-table th {
background-color: #104E8B;
color: #FFF;
font-weight: bold;
}
</style><table class="tableizer-table">

<tr class="tableizer-firstrow"><th>1</th><th>A</th><th>B</th><th>D</th><th>F</th></tr>
<tr><td>2</td><td>Sport</td><td>SHOP A</td><td>SHOP B </td><td>SHOP C</td></tr>
<tr><td>4</td><td>American football</td><td>2475</td><td>354</td><td>71</td></tr>
<tr><td>5</td><td>Badminton</td><td>45940</td><td>7404</td><td>4858</td></tr>
<tr><td>6</td><td>Baseball & softball</td><td>1698</td><td>140</td><td>248</td></tr>
<tr><td>7</td><td>Basketball</td><td>8505</td><td>2118</td><td>911</td></tr>
<tr><td>8</td><td>Boxing</td><td>32711</td><td>3694</td><td>3556</td></tr>
<tr><td>9</td><td>Cricket</td><td>29014</td><td>5066</td><td>908</td></tr>
<tr><td>10</td><td>Darts</td><td>27770</td><td>3921</td><td>236</td></tr>
<tr><td>11</td><td>Fitness</td><td>102230</td><td>25123</td><td>13938</td></tr>
<tr><td>12</td><td>Football</td><td>62940</td><td>17685</td><td>12241</td></tr>
<tr><td>13</td><td>Footwear</td><td>126736</td><td>19233</td><td>20603</td></tr>
<tr><td>14</td><td>Games and tables</td><td>430</td><td>285</td><td>0</td></tr>
<tr><td>15</td><td>Handball</td><td>1252</td><td>105</td><td>212</td></tr>
<tr><td>16</td><td>Hockey</td><td>58462</td><td>11190</td><td>2184</td></tr>
<tr><td>17</td><td>Martial Arts</td><td>2213</td><td>444</td><td>635</td></tr>
<tr><td>18</td><td>Netball</td><td>12712</td><td>4747</td><td>468</td></tr>
<tr><td>19</td><td>Outdoor</td><td>408194</td><td>118407</td><td>28967</td></tr>
<tr><td>20</td><td>Racketball</td><td>1384</td><td>259</td><td>185</td></tr>
<tr><td>21</td><td>Rounders</td><td>579</td><td>10</td><td>0</td></tr>
<tr><td>22</td><td>Rugby</td><td>82928</td><td>34757</td><td>5621</td></tr>
<tr><td>23</td><td>Running</td><td>1066408</td><td>229649</td><td>105354</td></tr>
<tr><td>24</td><td>Squash</td><td>43260</td><td>8724</td><td>4483</td></tr>
<tr><td>25</td><td>Swimming</td><td>37908</td><td>8324</td><td>5814</td></tr>
<tr><td>26</td><td>Table tennis</td><td>9003</td><td>2458</td><td>333</td></tr>
<tr><td>27</td><td>Tennis</td><td>195543</td><td>24800</td><td>31310</td></tr>
<tr><td>28</td><td>Triathlon</td><td>87640</td><td>8757</td><td>4620</td></tr>
<tr><td>29</td><td>Weightlifting</td><td>13453</td><td>4337</td><td>871</td></tr>
<tr><td>30</td><td>Wrestling</td><td>4412</td><td>569</td><td>404</td></tr>
</table>

 

[steve the fish]

Try

=INDEX(A1:AA100,MATCH("Darts",A1:A100,0),MATCH("Shop C",A1:AA1,0))

Change to suit.

This allows 100 rows and 27 columns. You could change 'darts' or 'shop c' to cell references if required.

 

[nickpape]

Thanks for this. Great help.

To add another layer to this though, I am trying to use the INDEX + MATCH formula to present the data in the following layout yet I find myself manually transposing the data!

See below the example of how I am to create the layout:

[TABLE="class: tableizer-table"]
<tbody>[TR="class: tableizer-firstrow"]
[TH][/TH]
[TH]A [/TH]
[TH]B[/TH]
[TH]C
[/TH]
[/TR]
[TR]
[TD]1[/TD]
[TD]sport[/TD]
[TD]shop[/TD]
[TD]sales[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]American football[/TD]
[TD]Shop A[/TD]
[TD]2475[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]American football[/TD]
[TD]Shop B
[/TD]
[TD]354[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]American football[/TD]
[TD]Shop C[/TD]
[TD]71
[/TD]
[/TR]
</tbody>[/TABLE]

Thanks