1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.
  2. Hi Guest, welcome to the TES Community!

    Connect with like-minded professionals and have your say on the issues that matter to you.

    Don't forget to look at the how to guide.

    Dismiss Notice

Index Match problem with Excel.

Discussion in 'Computing and ICT' started by DEmsley, May 1, 2012.

  1. DEmsley

    DEmsley New commenter

    OK, brain fade time. OCR Nationals, Number of GCSEs 1 to 4, points score and need to return the grade.
    In a spreadsheet I have a lookup table:
    <table cellpadding="0" cellspacing="0" style="width:208pt;border-collapse:collapse;"><tr style="height:12.75pt;"><td style="background-color:transparent;width:32pt;height:12.75pt;border:windowtext 0.5pt solid;" class="xl66"><a name="RANGE!F9:J13">[/URL] </td><td style="border-bottom:windowtext 0.5pt solid;border-left:windowtext;background-color:transparent;width:34pt;border-top:windowtext 0.5pt solid;border-right:windowtext 0.5pt solid;" class="xl67">F</td><td style="border-bottom:windowtext 0.5pt solid;border-left:windowtext;background-color:transparent;width:48pt;border-top:windowtext 0.5pt solid;border-right:windowtext 0.5pt solid;" class="xl67">P</td><td style="border-bottom:windowtext 0.5pt solid;border-left:windowtext;background-color:transparent;width:28pt;border-top:windowtext 0.5pt solid;border-right:windowtext 0.5pt solid;" class="xl66">M</td><td style="border-bottom:windowtext 0.5pt solid;border-left:windowtext;background-color:transparent;width:66pt;border-top:windowtext 0.5pt solid;border-right:windowtext 0.5pt solid;" class="xl66">D</td></tr><tr style="height:12.75pt;"><td style="border-bottom:windowtext 0.5pt solid;border-left:windowtext 0.5pt solid;background-color:transparent;height:12.75pt;border-top:windowtext;border-right:windowtext 0.5pt solid;" class="xl66" align="right">1</td><td style="border-bottom:windowtext 0.5pt solid;border-left:windowtext;background-color:transparent;border-top:windowtext;border-right:windowtext 0.5pt solid;" class="xl66" align="right">0</td><td style="border-bottom:windowtext 0.5pt solid;border-left:windowtext;background-color:transparent;border-top:windowtext;border-right:windowtext 0.5pt solid;" class="xl66" align="right">1.5</td><td style="border-bottom:windowtext 0.5pt solid;border-left:windowtext;background-color:transparent;border-top:windowtext;border-right:windowtext 0.5pt solid;" class="xl66" align="right">2.5</td><td style="border-bottom:windowtext 0.5pt solid;border-left:windowtext;background-color:transparent;border-top:windowtext;border-right:windowtext 0.5pt solid;" class="xl66" align="right">3.5</td></tr><tr style="height:12.75pt;"><td style="border-bottom:windowtext 0.5pt solid;border-left:windowtext 0.5pt solid;background-color:transparent;height:12.75pt;border-top:windowtext;border-right:windowtext 0.5pt solid;" class="xl66" align="right">2</td><td style="border-bottom:windowtext 0.5pt solid;border-left:windowtext;background-color:transparent;border-top:windowtext;border-right:windowtext 0.5pt solid;" class="xl66" align="right">0</td><td style="border-bottom:windowtext 0.5pt solid;border-left:windowtext;background-color:transparent;border-top:windowtext;border-right:windowtext 0.5pt solid;" class="xl66" align="right">3</td><td style="border-bottom:windowtext 0.5pt solid;border-left:windowtext;background-color:transparent;border-top:windowtext;border-right:windowtext 0.5pt solid;" class="xl66" align="right">4.5</td><td style="border-bottom:windowtext 0.5pt solid;border-left:windowtext;background-color:transparent;border-top:windowtext;border-right:windowtext 0.5pt solid;" class="xl66" align="right">7</td></tr><tr style="height:12.75pt;"><td style="border-bottom:windowtext 0.5pt solid;border-left:windowtext 0.5pt solid;background-color:transparent;height:12.75pt;border-top:windowtext;border-right:windowtext 0.5pt solid;" class="xl66" align="right">3</td><td style="border-bottom:windowtext 0.5pt solid;border-left:windowtext;background-color:transparent;border-top:windowtext;border-right:windowtext 0.5pt solid;" class="xl66" align="right">0</td><td style="border-bottom:windowtext 0.5pt solid;border-left:windowtext;background-color:transparent;border-top:windowtext;border-right:windowtext 0.5pt solid;" class="xl66" align="right">4.5</td><td style="border-bottom:windowtext 0.5pt solid;border-left:windowtext;background-color:transparent;border-top:windowtext;border-right:windowtext 0.5pt solid;" class="xl66" align="right">6.5</td><td style="border-bottom:windowtext 0.5pt solid;border-left:windowtext;background-color:transparent;border-top:windowtext;border-right:windowtext 0.5pt solid;" class="xl66" align="right">10.5</td></tr><tr style="height:12.75pt;"><td style="border-bottom:windowtext 0.5pt solid;border-left:windowtext 0.5pt solid;background-color:transparent;height:12.75pt;border-top:windowtext;border-right:windowtext 0.5pt solid;" class="xl66" align="right">4</td><td style="border-bottom:windowtext 0.5pt solid;border-left:windowtext;background-color:transparent;border-top:windowtext;border-right:windowtext 0.5pt solid;" class="xl66" align="right">0</td><td style="border-bottom:windowtext 0.5pt solid;border-left:windowtext;background-color:transparent;border-top:windowtext;border-right:windowtext 0.5pt solid;" class="xl66" align="right">6</td><td style="border-bottom:windowtext 0.5pt solid;border-left:windowtext;background-color:transparent;border-top:windowtext;border-right:windowtext 0.5pt solid;" class="xl66" align="right">8.5</td><td style="border-bottom:windowtext 0.5pt solid;border-left:windowtext;background-color:transparent;border-top:windowtext;border-right:windowtext 0.5pt solid;" class="xl66" align="right">14</td></tr></table>
    ...and I'm struggling to set up the index/match function to return the grade given the points and number of GCSEs entered for.

    I'd be grateful for any assistance on this.
     
  2. DEmsley

    DEmsley New commenter

    OK, brain fade time. OCR Nationals, Number of GCSEs 1 to 4, points score and need to return the grade.
    In a spreadsheet I have a lookup table:
    <table cellpadding="0" cellspacing="0" style="width:208pt;border-collapse:collapse;"><tr style="height:12.75pt;"><td style="background-color:transparent;width:32pt;height:12.75pt;border:windowtext 0.5pt solid;" class="xl66"><a name="RANGE!F9:J13">[/URL] </td><td style="border-bottom:windowtext 0.5pt solid;border-left:windowtext;background-color:transparent;width:34pt;border-top:windowtext 0.5pt solid;border-right:windowtext 0.5pt solid;" class="xl67">F</td><td style="border-bottom:windowtext 0.5pt solid;border-left:windowtext;background-color:transparent;width:48pt;border-top:windowtext 0.5pt solid;border-right:windowtext 0.5pt solid;" class="xl67">P</td><td style="border-bottom:windowtext 0.5pt solid;border-left:windowtext;background-color:transparent;width:28pt;border-top:windowtext 0.5pt solid;border-right:windowtext 0.5pt solid;" class="xl66">M</td><td style="border-bottom:windowtext 0.5pt solid;border-left:windowtext;background-color:transparent;width:66pt;border-top:windowtext 0.5pt solid;border-right:windowtext 0.5pt solid;" class="xl66">D</td></tr><tr style="height:12.75pt;"><td style="border-bottom:windowtext 0.5pt solid;border-left:windowtext 0.5pt solid;background-color:transparent;height:12.75pt;border-top:windowtext;border-right:windowtext 0.5pt solid;" class="xl66" align="right">1</td><td style="border-bottom:windowtext 0.5pt solid;border-left:windowtext;background-color:transparent;border-top:windowtext;border-right:windowtext 0.5pt solid;" class="xl66" align="right">0</td><td style="border-bottom:windowtext 0.5pt solid;border-left:windowtext;background-color:transparent;border-top:windowtext;border-right:windowtext 0.5pt solid;" class="xl66" align="right">1.5</td><td style="border-bottom:windowtext 0.5pt solid;border-left:windowtext;background-color:transparent;border-top:windowtext;border-right:windowtext 0.5pt solid;" class="xl66" align="right">2.5</td><td style="border-bottom:windowtext 0.5pt solid;border-left:windowtext;background-color:transparent;border-top:windowtext;border-right:windowtext 0.5pt solid;" class="xl66" align="right">3.5</td></tr><tr style="height:12.75pt;"><td style="border-bottom:windowtext 0.5pt solid;border-left:windowtext 0.5pt solid;background-color:transparent;height:12.75pt;border-top:windowtext;border-right:windowtext 0.5pt solid;" class="xl66" align="right">2</td><td style="border-bottom:windowtext 0.5pt solid;border-left:windowtext;background-color:transparent;border-top:windowtext;border-right:windowtext 0.5pt solid;" class="xl66" align="right">0</td><td style="border-bottom:windowtext 0.5pt solid;border-left:windowtext;background-color:transparent;border-top:windowtext;border-right:windowtext 0.5pt solid;" class="xl66" align="right">3</td><td style="border-bottom:windowtext 0.5pt solid;border-left:windowtext;background-color:transparent;border-top:windowtext;border-right:windowtext 0.5pt solid;" class="xl66" align="right">4.5</td><td style="border-bottom:windowtext 0.5pt solid;border-left:windowtext;background-color:transparent;border-top:windowtext;border-right:windowtext 0.5pt solid;" class="xl66" align="right">7</td></tr><tr style="height:12.75pt;"><td style="border-bottom:windowtext 0.5pt solid;border-left:windowtext 0.5pt solid;background-color:transparent;height:12.75pt;border-top:windowtext;border-right:windowtext 0.5pt solid;" class="xl66" align="right">3</td><td style="border-bottom:windowtext 0.5pt solid;border-left:windowtext;background-color:transparent;border-top:windowtext;border-right:windowtext 0.5pt solid;" class="xl66" align="right">0</td><td style="border-bottom:windowtext 0.5pt solid;border-left:windowtext;background-color:transparent;border-top:windowtext;border-right:windowtext 0.5pt solid;" class="xl66" align="right">4.5</td><td style="border-bottom:windowtext 0.5pt solid;border-left:windowtext;background-color:transparent;border-top:windowtext;border-right:windowtext 0.5pt solid;" class="xl66" align="right">6.5</td><td style="border-bottom:windowtext 0.5pt solid;border-left:windowtext;background-color:transparent;border-top:windowtext;border-right:windowtext 0.5pt solid;" class="xl66" align="right">10.5</td></tr><tr style="height:12.75pt;"><td style="border-bottom:windowtext 0.5pt solid;border-left:windowtext 0.5pt solid;background-color:transparent;height:12.75pt;border-top:windowtext;border-right:windowtext 0.5pt solid;" class="xl66" align="right">4</td><td style="border-bottom:windowtext 0.5pt solid;border-left:windowtext;background-color:transparent;border-top:windowtext;border-right:windowtext 0.5pt solid;" class="xl66" align="right">0</td><td style="border-bottom:windowtext 0.5pt solid;border-left:windowtext;background-color:transparent;border-top:windowtext;border-right:windowtext 0.5pt solid;" class="xl66" align="right">6</td><td style="border-bottom:windowtext 0.5pt solid;border-left:windowtext;background-color:transparent;border-top:windowtext;border-right:windowtext 0.5pt solid;" class="xl66" align="right">8.5</td><td style="border-bottom:windowtext 0.5pt solid;border-left:windowtext;background-color:transparent;border-top:windowtext;border-right:windowtext 0.5pt solid;" class="xl66" align="right">14</td></tr></table>
    ...and I'm struggling to set up the index/match function to return the grade given the points and number of GCSEs entered for.

    I'd be grateful for any assistance on this.
     
  3. Personally I'd transpose that table:
    <table cellpadding="0" cellspacing="0" style="width:240pt;border-collapse:collapse;"><tr style="height:15pt;"><td style="width:48pt;height:15pt;background-color:transparent;border:windowtext 0.5pt solid;" class="xl64">1</td><td style="border-right:windowtext 0.5pt solid;border-top:windowtext 0.5pt solid;border-left:windowtext;width:48pt;border-bottom:windowtext 0.5pt solid;background-color:transparent;" class="xl64">2</td><td style="border-right:windowtext 0.5pt solid;border-top:windowtext 0.5pt solid;border-left:windowtext;width:48pt;border-bottom:windowtext 0.5pt solid;background-color:transparent;" class="xl64">3</td><td style="border-right:windowtext 0.5pt solid;border-top:windowtext 0.5pt solid;border-left:windowtext;width:48pt;border-bottom:windowtext 0.5pt solid;background-color:transparent;" class="xl64">4</td><td style="border-right:windowtext 0.5pt solid;border-top:windowtext 0.5pt solid;border-left:windowtext;width:48pt;border-bottom:windowtext 0.5pt solid;background-color:transparent;" class="xl63"><tr style="height:15pt;"><td style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext 0.5pt solid;width:48pt;border-bottom:windowtext 0.5pt solid;height:15pt;background-color:transparent;" class="xl64">0</td><td style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext;width:48pt;border-bottom:windowtext 0.5pt solid;background-color:transparent;" class="xl64">0</td><td style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext;width:48pt;border-bottom:windowtext 0.5pt solid;background-color:transparent;" class="xl64">0</td><td style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext;width:48pt;border-bottom:windowtext 0.5pt solid;background-color:transparent;" class="xl64">0</td><td style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext;width:48pt;border-bottom:windowtext 0.5pt solid;background-color:transparent;" class="xl63">F</td></tr><tr style="height:15pt;"><td style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext 0.5pt solid;width:48pt;border-bottom:windowtext 0.5pt solid;height:15pt;background-color:transparent;" class="xl64">1.5</td><td style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext;width:48pt;border-bottom:windowtext 0.5pt solid;background-color:transparent;" class="xl64">3</td><td style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext;width:48pt;border-bottom:windowtext 0.5pt solid;background-color:transparent;" class="xl64">4.5</td><td style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext;width:48pt;border-bottom:windowtext 0.5pt solid;background-color:transparent;" class="xl64">6</td><td style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext;width:48pt;border-bottom:windowtext 0.5pt solid;background-color:transparent;" class="xl63">P</td></tr><tr style="height:15pt;"><td style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext 0.5pt solid;width:48pt;border-bottom:windowtext 0.5pt solid;height:15pt;background-color:transparent;" class="xl64">2.5</td><td style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext;width:48pt;border-bottom:windowtext 0.5pt solid;background-color:transparent;" class="xl64">4.5</td><td style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext;width:48pt;border-bottom:windowtext 0.5pt solid;background-color:transparent;" class="xl64">6.5</td><td style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext;width:48pt;border-bottom:windowtext 0.5pt solid;background-color:transparent;" class="xl64">8.5</td><td style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext;width:48pt;border-bottom:windowtext 0.5pt solid;background-color:transparent;" class="xl63">M</td></tr><tr style="height:15pt;"><td style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext 0.5pt solid;width:48pt;border-bottom:windowtext 0.5pt solid;height:15pt;background-color:transparent;" class="xl64">3.5</td><td style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext;width:48pt;border-bottom:windowtext 0.5pt solid;background-color:transparent;" class="xl64">7</td><td style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext;width:48pt;border-bottom:windowtext 0.5pt solid;background-color:transparent;" class="xl64">10.5</td><td style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext;width:48pt;border-bottom:windowtext 0.5pt solid;background-color:transparent;" class="xl64">14 </td><td style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext;width:48pt;border-bottom:windowtext 0.5pt solid;background-color:transparent;" class="xl63">D</td></tr></td></tr></table>
    Assuming that this table begins at A1 you can use: =OFFSET(E1,MATCH([points],OFFSET(A2,0,[GCSEs]-1,4,1)),0)
    Replace the bits in bold square brackets with the relevant data. I think that should work.
     
  4. DEmsley

    DEmsley New commenter

    On some forums when you get great help you can award brownie points, shame you can't here.
    You're a star violetriga, many thanks. [​IMG]

     

Share This Page