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
  3. The Teacher Q&A will be closing soon.

    If you have any information that you would like to keep or refer to in the future please can you copy and paste the information to a format suitable for you to save or take screen shots of the questions and responses you are interested in.

    Don’t forget you can still use the rest of the forums on theTes Community to post questions and get the advice, help and support you require from your peers for all your teaching needs.

    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