Index and Match Probelm

Joneye · May 19, 2011

In the mastersheet tab I have a formula in cell D4 copied down to D57. The formula seems to fail from cells D34 - D57 -(help needed here & possiblly why would help me for future learning).

Second prblem is switching to the data tab I wish to select C1 and change it to SEVEN once this is done the whole formula fails.

Any help or advice is always appreciated.

Below is my problem file - hosted on SKYDRIVE

http://cid-7fe4ae1eb3f94c4e.office.live.com/self.aspx/Mr%20Excel/PROBLEM%5E_DOC.xls

Akashwani · May 19, 2011

Hi,

Just a guess, is the range within your formula accurate?

As in $A$2:$A$100 when it should be say $A$2:$A$1000

Ak

Joneye · May 19, 2011

Hi,

Just checked as far as my eyes can see Ranges are fine. Im thinking its either MATCH / Index does not like the wording its looking up or perhaps there is some problem with the CELLs ?

anglais428 · May 19, 2011

=IF(ISBLANK(data!E5),"0",INDEX(data!$E$5:$AT$34,MATCH('Master Sheet'!B4,data!$C$5:$C$34,0),MATCH(data!$C$1,data!$E$3:$G$3,0)))

Just changed the ranges. Is this the result you want?

Joneye · May 19, 2011

Hi,

Just copied the formula in you have posted - oddly its failing below is my data...

Still not sure why it fails From D34 onwards.

Excel 2003<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>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">2</td><td style="color: #FFFFFF;background-color: #000000;;">House</td><td style="text-align: right;;"></td><td style="text-align: center;color: #FFFFFF;background-color: #000000;;">Quantity</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="border-bottom: 1px solid black;color: #FFFFFF;background-color: #000000;;"></td><td style="text-align: right;;"></td><td style="text-align: center;color: #FFFFFF;background-color: #000000;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Evesham</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Hexham</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Broadway</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Durham</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Letchworth</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Ludlow</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Warwick</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Shrewsbury</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Kenilworth [M]</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Kenilworth [E]</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Stratford</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Windsor</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Oxford</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Kensington [E]</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Kensington [M]</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Cambridge</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">York [M]</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">York [E]</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Chester</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Lincoln</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Lincoln [Alt]</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Canterbury</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Winchester</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Salisbury</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">28</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Westminster</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">29</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Balmoral</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">30</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Hampstead 5</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">31</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Hampstead 5 [Alt]</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">32</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Hampstead 6</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">33</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Hampstead 6 [Alt]</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">34</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Richmond</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: center;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">35</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Blenheim</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: center;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">36</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Shrewsbury+</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: center;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">37</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Windsor+</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: center;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">38</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Oxford+</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: center;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">39</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Chester+</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: center;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">40</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Worcester+</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: center;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">41</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Kensington+ [E]</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: center;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">42</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Kensington+ [M]</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: center;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">43</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Coniston</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: center;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">44</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Brecon</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: center;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">45</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Brecon2</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: center;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">46</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Pembroke</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: center;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">47</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Pembroke [Alt]</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: center;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">48</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Ruthin</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: center;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">49</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Hayeswater 1F5</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: center;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">50</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Hayeswater 2F6</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: center;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">51</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Hayeswater 2F7</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: center;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">52</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Hayeswater 1G5</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: center;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">53</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Hayeswater 2G6</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: center;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">54</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Hayeswater 2G7</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: center;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">55</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Hayeswater 1S5</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: center;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">56</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Hayeswater 2S6</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: center;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">57</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Hayeswater 2S7</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: center;;">#N/A</td></tr></tbody></table>Master Sheet <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" >Worksheet Formulas<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">D4</th><td style="text-align:left">=IF(ISBLANK(data!E5),"0",INDEX(data!$E$5:$AT$34,MATCH('Master Sheet'!B4,data!$C$5:$C$34,0),MATCH(data!$C$1,data!$E$3:$G$3,0)))</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D5</th><td style="text-align:left">=IF(ISBLANK(data!E6),"0",INDEX(data!$E$5:$AT$34,MATCH('Master Sheet'!B5,data!$C$5:$C$34,0),MATCH(data!$C$1,data!$E$3:$G$3,0)))</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D6</th><td style="text-align:left">=IF(ISBLANK(data!E7),"0",INDEX(data!$E$5:$AT$34,MATCH('Master Sheet'!B6,data!$C$5:$C$34,0),MATCH(data!$C$1,data!$E$3:$G$3,0)))</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D7</th><td style="text-align:left">=IF(ISBLANK(data!E8),"0",INDEX(data!$E$5:$AT$34,MATCH('Master Sheet'!B7,data!$C$5:$C$34,0),MATCH(data!$C$1,data!$E$3:$G$3,0)))</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D8</th><td style="text-align:left">=IF(ISBLANK(data!E9),"0",INDEX(data!$E$5:$AT$34,MATCH('Master Sheet'!B8,data!$C$5:$C$34,0),MATCH(data!$C$1,data!$E$3:$G$3,0)))</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D9</th><td style="text-align:left">=IF(ISBLANK(data!E10),"0",INDEX(data!$E$5:$AT$34,MATCH('Master Sheet'!B9,data!$C$5:$C$34,0),MATCH(data!$C$1,data!$E$3:$G$3,0)))</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D10</th><td style="text-align:left">=IF(ISBLANK(data!E11),"0",INDEX(data!$E$5:$AT$34,MATCH('Master Sheet'!B10,data!$C$5:$C$34,0),MATCH(data!$C$1,data!$E$3:$G$3,0)))</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D11</th><td style="text-align:left">=IF(ISBLANK(data!E12),"0",INDEX(data!$E$5:$AT$34,MATCH('Master Sheet'!B11,data!$C$5:$C$34,0),MATCH(data!$C$1,data!$E$3:$G$3,0)))</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D12</th><td style="text-align:left">=IF(ISBLANK(data!E13),"0",INDEX(data!$E$5:$AT$34,MATCH('Master Sheet'!B12,data!$C$5:$C$34,0),MATCH(data!$C$1,data!$E$3:$G$3,0)))</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D13</th><td style="text-align:left">=IF(ISBLANK(data!E14),"0",INDEX(data!$E$5:$AT$34,MATCH('Master Sheet'!B13,data!$C$5:$C$34,0),MATCH(data!$C$1,data!$E$3:$G$3,0)))</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D14</th><td style="text-align:left">=IF(ISBLANK(data!E15),"0",INDEX(data!$E$5:$AT$34,MATCH('Master Sheet'!B14,data!$C$5:$C$34,0),MATCH(data!$C$1,data!$E$3:$G$3,0)))</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D15</th><td style="text-align:left">=IF(ISBLANK(data!E16),"0",INDEX(data!$E$5:$AT$34,MATCH('Master Sheet'!B15,data!$C$5:$C$34,0),MATCH(data!$C$1,data!$E$3:$G$3,0)))</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D16</th><td style="text-align:left">=IF(ISBLANK(data!E17),"0",INDEX(data!$E$5:$AT$34,MATCH('Master Sheet'!B16,data!$C$5:$C$34,0),MATCH(data!$C$1,data!$E$3:$G$3,0)))</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D17</th><td style="text-align:left">=IF(ISBLANK(data!E18),"0",INDEX(data!$E$5:$AT$34,MATCH('Master Sheet'!B17,data!$C$5:$C$34,0),MATCH(data!$C$1,data!$E$3:$G$3,0)))</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D18</th><td style="text-align:left">=IF(ISBLANK(data!E19),"0",INDEX(data!$E$5:$AT$34,MATCH('Master Sheet'!B18,data!$C$5:$C$34,0),MATCH(data!$C$1,data!$E$3:$G$3,0)))</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D19</th><td style="text-align:left">=IF(ISBLANK(data!E20),"0",INDEX(data!$E$5:$AT$34,MATCH('Master Sheet'!B19,data!$C$5:$C$34,0),MATCH(data!$C$1,data!$E$3:$G$3,0)))</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D20</th><td style="text-align:left">=IF(ISBLANK(data!E21),"0",INDEX(data!$E$5:$AT$34,MATCH('Master Sheet'!B20,data!$C$5:$C$34,0),MATCH(data!$C$1,data!$E$3:$G$3,0)))</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D21</th><td style="text-align:left">=IF(ISBLANK(data!E22),"0",INDEX(data!$E$5:$AT$34,MATCH('Master Sheet'!B21,data!$C$5:$C$34,0),MATCH(data!$C$1,data!$E$3:$G$3,0)))</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D22</th><td style="text-align:left">=IF(ISBLANK(data!E23),"0",INDEX(data!$E$5:$AT$34,MATCH('Master Sheet'!B22,data!$C$5:$C$34,0),MATCH(data!$C$1,data!$E$3:$G$3,0)))</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D23</th><td style="text-align:left">=IF(ISBLANK(data!E24),"0",INDEX(data!$E$5:$AT$34,MATCH('Master Sheet'!B23,data!$C$5:$C$34,0),MATCH(data!$C$1,data!$E$3:$G$3,0)))</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D24</th><td style="text-align:left">=IF(ISBLANK(data!E25),"0",INDEX(data!$E$5:$AT$34,MATCH('Master Sheet'!B24,data!$C$5:$C$34,0),MATCH(data!$C$1,data!$E$3:$G$3,0)))</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D25</th><td style="text-align:left">=IF(ISBLANK(data!E26),"0",INDEX(data!$E$5:$AT$34,MATCH('Master Sheet'!B25,data!$C$5:$C$34,0),MATCH(data!$C$1,data!$E$3:$G$3,0)))</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D26</th><td style="text-align:left">=IF(ISBLANK(data!E27),"0",INDEX(data!$E$5:$AT$34,MATCH('Master Sheet'!B26,data!$C$5:$C$34,0),MATCH(data!$C$1,data!$E$3:$G$3,0)))</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D27</th><td style="text-align:left">=IF(ISBLANK(data!E28),"0",INDEX(data!$E$5:$AT$34,MATCH('Master Sheet'!B27,data!$C$5:$C$34,0),MATCH(data!$C$1,data!$E$3:$G$3,0)))</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D28</th><td style="text-align:left">=IF(ISBLANK(data!E29),"0",INDEX(data!$E$5:$AT$34,MATCH('Master Sheet'!B28,data!$C$5:$C$34,0),MATCH(data!$C$1,data!$E$3:$G$3,0)))</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D29</th><td style="text-align:left">=IF(ISBLANK(data!E30),"0",INDEX(data!$E$5:$AT$34,MATCH('Master Sheet'!B29,data!$C$5:$C$34,0),MATCH(data!$C$1,data!$E$3:$G$3,0)))</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D30</th><td style="text-align:left">=IF(ISBLANK(data!E31),"0",INDEX(data!$E$5:$AT$34,MATCH('Master Sheet'!B30,data!$C$5:$C$34,0),MATCH(data!$C$1,data!$E$3:$G$3,0)))</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D31</th><td style="text-align:left">=IF(ISBLANK(data!E32),"0",INDEX(data!$E$5:$AT$34,MATCH('Master Sheet'!B31,data!$C$5:$C$34,0),MATCH(data!$C$1,data!$E$3:$G$3,0)))</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D32</th><td style="text-align:left">=IF(ISBLANK(data!E33),"0",INDEX(data!$E$5:$AT$34,MATCH('Master Sheet'!B32,data!$C$5:$C$34,0),MATCH(data!$C$1,data!$E$3:$G$3,0)))</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D33</th><td style="text-align:left">=IF(ISBLANK(data!E34),"0",INDEX(data!$E$5:$AT$34,MATCH('Master Sheet'!B33,data!$C$5:$C$34,0),MATCH(data!$C$1,data!$E$3:$G$3,0)))</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D34</th><td style="text-align:left">=IF(ISBLANK(data!E35),"0",INDEX(data!$E$5:$AT$34,MATCH('Master Sheet'!B34,data!$C$5:$C$34,0),MATCH(data!$C$1,data!$E$3:$G$3,0)))</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D35</th><td style="text-align:left">=IF(ISBLANK(data!E36),"0",INDEX(data!$E$5:$AT$34,MATCH('Master Sheet'!B35,data!$C$5:$C$34,0),MATCH(data!$C$1,data!$E$3:$G$3,0)))</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D36</th><td style="text-align:left">=IF(ISBLANK(data!E37),"0",INDEX(data!$E$5:$AT$34,MATCH('Master Sheet'!B36,data!$C$5:$C$34,0),MATCH(data!$C$1,data!$E$3:$G$3,0)))</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D37</th><td style="text-align:left">=IF(ISBLANK(data!E38),"0",INDEX(data!$E$5:$AT$34,MATCH('Master Sheet'!B37,data!$C$5:$C$34,0),MATCH(data!$C$1,data!$E$3:$G$3,0)))</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D38</th><td style="text-align:left">=IF(ISBLANK(data!E39),"0",INDEX(data!$E$5:$AT$34,MATCH('Master Sheet'!B38,data!$C$5:$C$34,0),MATCH(data!$C$1,data!$E$3:$G$3,0)))</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D39</th><td style="text-align:left">=IF(ISBLANK(data!E40),"0",INDEX(data!$E$5:$AT$34,MATCH('Master Sheet'!B39,data!$C$5:$C$34,0),MATCH(data!$C$1,data!$E$3:$G$3,0)))</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D40</th><td style="text-align:left">=IF(ISBLANK(data!E41),"0",INDEX(data!$E$5:$AT$34,MATCH('Master Sheet'!B40,data!$C$5:$C$34,0),MATCH(data!$C$1,data!$E$3:$G$3,0)))</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D41</th><td style="text-align:left">=IF(ISBLANK(data!E42),"0",INDEX(data!$E$5:$AT$34,MATCH('Master Sheet'!B41,data!$C$5:$C$34,0),MATCH(data!$C$1,data!$E$3:$G$3,0)))</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D42</th><td style="text-align:left">=IF(ISBLANK(data!E43),"0",INDEX(data!$E$5:$AT$34,MATCH('Master Sheet'!B42,data!$C$5:$C$34,0),MATCH(data!$C$1,data!$E$3:$G$3,0)))</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D43</th><td style="text-align:left">=IF(ISBLANK(data!E44),"0",INDEX(data!$E$5:$AT$34,MATCH('Master Sheet'!B43,data!$C$5:$C$34,0),MATCH(data!$C$1,data!$E$3:$G$3,0)))</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D44</th><td style="text-align:left">=IF(ISBLANK(data!E45),"0",INDEX(data!$E$5:$AT$34,MATCH('Master Sheet'!B44,data!$C$5:$C$34,0),MATCH(data!$C$1,data!$E$3:$G$3,0)))</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D45</th><td style="text-align:left">=IF(ISBLANK(data!E46),"0",INDEX(data!$E$5:$AT$34,MATCH('Master Sheet'!B45,data!$C$5:$C$34,0),MATCH(data!$C$1,data!$E$3:$G$3,0)))</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D46</th><td style="text-align:left">=IF(ISBLANK(data!E47),"0",INDEX(data!$E$5:$AT$34,MATCH('Master Sheet'!B46,data!$C$5:$C$34,0),MATCH(data!$C$1,data!$E$3:$G$3,0)))</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D47</th><td style="text-align:left">=IF(ISBLANK(data!E48),"0",INDEX(data!$E$5:$AT$34,MATCH('Master Sheet'!B47,data!$C$5:$C$34,0),MATCH(data!$C$1,data!$E$3:$G$3,0)))</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D48</th><td style="text-align:left">=IF(ISBLANK(data!E49),"0",INDEX(data!$E$5:$AT$34,MATCH('Master Sheet'!B48,data!$C$5:$C$34,0),MATCH(data!$C$1,data!$E$3:$G$3,0)))</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D49</th><td style="text-align:left">=IF(ISBLANK(data!E50),"0",INDEX(data!$E$5:$AT$34,MATCH('Master Sheet'!B49,data!$C$5:$C$34,0),MATCH(data!$C$1,data!$E$3:$G$3,0)))</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D50</th><td style="text-align:left">=IF(ISBLANK(data!E51),"0",INDEX(data!$E$5:$AT$34,MATCH('Master Sheet'!B50,data!$C$5:$C$34,0),MATCH(data!$C$1,data!$E$3:$G$3,0)))</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D51</th><td style="text-align:left">=IF(ISBLANK(data!E52),"0",INDEX(data!$E$5:$AT$34,MATCH('Master Sheet'!B51,data!$C$5:$C$34,0),MATCH(data!$C$1,data!$E$3:$G$3,0)))</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D52</th><td style="text-align:left">=IF(ISBLANK(data!E53),"0",INDEX(data!$E$5:$AT$34,MATCH('Master Sheet'!B52,data!$C$5:$C$34,0),MATCH(data!$C$1,data!$E$3:$G$3,0)))</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D53</th><td style="text-align:left">=IF(ISBLANK(data!E54),"0",INDEX(data!$E$5:$AT$34,MATCH('Master Sheet'!B53,data!$C$5:$C$34,0),MATCH(data!$C$1,data!$E$3:$G$3,0)))</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D54</th><td style="text-align:left">=IF(ISBLANK(data!E55),"0",INDEX(data!$E$5:$AT$34,MATCH('Master Sheet'!B54,data!$C$5:$C$34,0),MATCH(data!$C$1,data!$E$3:$G$3,0)))</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D55</th><td style="text-align:left">=IF(ISBLANK(data!E56),"0",INDEX(data!$E$5:$AT$34,MATCH('Master Sheet'!B55,data!$C$5:$C$34,0),MATCH(data!$C$1,data!$E$3:$G$3,0)))</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D56</th><td style="text-align:left">=IF(ISBLANK(data!E57),"0",INDEX(data!$E$5:$AT$34,MATCH('Master Sheet'!B56,data!$C$5:$C$34,0),MATCH(data!$C$1,data!$E$3:$G$3,0)))</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D57</th><td style="text-align:left">=IF(ISBLANK(data!E58),"0",INDEX(data!$E$5:$AT$34,MATCH('Master Sheet'!B57,data!$C$5:$C$34,0),MATCH(data!$C$1,data!$E$3:$G$3,0)))</td></tr></tbody></table></td></tr></table>

anglais428 · May 19, 2011

hmmm, on my sheet all the results are "1" in that column. THe formula for richmond should be =IF(ISBLANK(data!E35),"0",INDEX(data!$E$5:$AT$58,MATCH('Master Sheet'!B34,data!$C$5:$C$58,0),MATCH(data!$C$1,data!$E$3:$G$3,0)))

Looks like you change the last part of your formula

Joneye · May 19, 2011

Very odd, im going to message you via here with my email.

Im sure there is something hard coded in within my workbook. If you can send me your changed one I would appreciate that.

Akashwani · May 19, 2011

Hi,

Does this work in D4?.....

=IF(ISBLANK(data!E5),"0",INDEX(data!$E$5:$AT$58,MATCH(B4,data!$C$5:$C$58,0),MATCH(data!$C$1,data!$E$3:$K$3,0)))

Ak

Joneye · May 19, 2011

GUYS

THANKS IT NOW WORKS - This has saved and helped me out loads, again many thanks to all that have helped....

Index and Match Probelm

Joneye

Well-known Member

Excel Facts

Akashwani

Well-known Member

Joneye

Well-known Member

anglais428

Well-known Member

Joneye

Well-known Member

anglais428

Well-known Member

Joneye

Well-known Member

Akashwani

Well-known Member

Joneye

Well-known Member

Similar threads

Forum statistics

Share this page

Index and Match Probelm

Well-known Member

Excel Facts

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock