Issue with MATCH dropdown #N/A

[stryder000]

Ok, I've tried searching everywhere for this answer but I cannot figure it out.
I have 2 sheets set up. On my first sheet I have set up two drop down boxes that relate to the X and Y axis of an index. The drop down box for my X axis works perfectly, however I'm having some issues with the Y axis.

The sheet the index is on is titled 'Multistorey Allow' and from A4 to A15 i have the following:
<table border="0" cellspacing="0" cols="1" frame="VOID" rules="NONE"> <colgroup><col width="227"></colgroup> <tbody> <tr> <td style="border: 1px solid rgb(0, 0, 0);" valign="MIDDLE" width="227" align="CENTER" bgcolor="#ccffff" height="17">No</td> </tr> <tr> <td style="border: 1px solid rgb(0, 0, 0);" valign="MIDDLE" align="JUSTIFY" bgcolor="#ccffff" height="18">Level 8 (CW/ECW 8)</td> </tr> <tr> <td style="border: 1px solid rgb(0, 0, 0);" valign="MIDDLE" align="JUSTIFY" bgcolor="#ccffff" height="18">Level 7 (CW/ECW 7)</td> </tr> <tr> <td style="border: 1px solid rgb(0, 0, 0);" valign="MIDDLE" align="JUSTIFY" bgcolor="#ccffff" height="18">Level 6 (CW/ECW 6)</td> </tr> <tr> <td style="border: 1px solid rgb(0, 0, 0);" valign="MIDDLE" align="JUSTIFY" bgcolor="#ccffff" height="18">Level 5 (CW/ECW 5)</td> </tr> <tr> <td style="border: 1px solid rgb(0, 0, 0);" valign="MIDDLE" align="JUSTIFY" bgcolor="#ccffff" height="18">Level 4 (CW/ECW 4)</td> </tr> <tr> <td style="border: 1px solid rgb(0, 0, 0);" valign="MIDDLE" align="JUSTIFY" bgcolor="#ccffff" height="18">Level 3 (CW/ECW 3)</td> </tr> <tr> <td style="border: 1px solid rgb(0, 0, 0);" valign="MIDDLE" align="JUSTIFY" bgcolor="#ccffff" height="18">Level 2 (CW/ECW 2)</td> </tr> <tr> <td style="border: 1px solid rgb(0, 0, 0);" valign="MIDDLE" align="JUSTIFY" bgcolor="#ccffff" height="34">Level 1 (CW/ECW 1): CW/ECW 1 (Level d)</td> </tr> <tr> <td style="border: 1px solid rgb(0, 0, 0);" valign="MIDDLE" align="JUSTIFY" bgcolor="#ccffff" height="34">Level 1 (CW/ECW 1): CW/ECW 1 (Level c)</td> </tr> <tr> <td style="border: 1px solid rgb(0, 0, 0);" valign="MIDDLE" align="JUSTIFY" bgcolor="#ccffff" height="34">Level 1 (CW/ECW 1): CW/ECW 1 (Level b)</td> </tr> <tr> <td style="border-top: 1px solid rgb(0, 0, 0); border-left: 1px solid rgb(0, 0, 0); border-right: 1px solid rgb(0, 0, 0);" valign="MIDDLE" align="JUSTIFY" bgcolor="#ccffff" height="34">Level 1 (CW/ECW 1): CW/ECW 1 (Level a)</td> </tr> </tbody> </table>
On my main sheet, I set up a drop down box in D7 to relate back to these for a 2dimensional index, and I made the formula =MATCH(D7;'Multistorey Allow'.A4:A15) to look these up, however, most of the time, except for 1,2 and 7, I get a value of #N/A when i select values in the dropdown box. Anyone got any ideas why I am getting #N/A for most of these?

 

[Mike Blackman]

Hi,

Maybe you need to set the MATCH to look for an exact match and also maybe your problems are coming from the fact you haven't anchored your MATCH formula: Maybe something like this?

=MATCH(D7;'Multistorey Allow'.$A$4:$A$15;0)

 

[stryder000]

Thou art a hero!!! Cheers for that.