Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Feb 2007
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Looking up specific results from a table (Excel 20

    Hi.

    Im trying to get a spreadsheet to look up specific data for me.
    I want to enter a persons age and their test result and for the spreadsheet to compare that to the appropriate previous test results (for their age) and then return the persons score.
    Someone suggested using Index and Match functions but I didnt see how to change their example in to doing what I wanted. I've attached a spreadsheet showing what I am aiming for.

    any help explainig how to do this would be great.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Looking up specific results from a table (Excel 20

    Welcome to Woody's Lounge!

    You can use the following formula:

    =INDEX($I$5:$M$5,MATCH(B7,OFFSET($I$6:$M$6,MATCH(A 7,$H$7:$H$13),0)))

    MATCH(A7,$H$7:$H$13), looks up the age in H7:H13 and returns the index of the cell where it is found (1=first cell, 2=sencond cell etc.)

    OFFSET($I$6:$M$6,MATCH(A7,$H$7:$H$13),0) shifts I6:M6 as many rows down as indicated by the result of MATCH. So for age=20, it is shofted 1 row down, etc. This results in the row with the test results for the specified age.

    MATCH(B7,OFFSET($I$6:$M$6,MATCH(A7,$H$7:$H$13),0)) looks up the test result in this row and returns the index of the cell containing the largest value that is smaller than or equal to the test result.

    INDEX($I$5:$M$5,MATCH(B7,OFFSET($I$6:$M$6,MATCH(A7 ,$H$7:$H$13),0))) returns the cell in I5:M5 in the same position, i.e. the score.

  3. #3
    New Lounger
    Join Date
    Feb 2007
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Looking up specific results from a table (Exce

    hey thanks.

    When i put this in, and then enter a name and a test result i get

    #name?

    Im not sure why though?

  4. #4
    New Lounger
    Join Date
    Feb 2007
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Looking up specific results from a table (Exce

    osrry.
    was trying it in open office.

    when i put it in to excel it works fine.

    thanks.

    if i want to expand the data set i can just increase the values it returns to like with any other formula? and if i want to hide the data on another sheet i can do that just the way I would on a different spreadsheet?

    Thanks.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Looking up specific results from a table (Exce

    Yes, you can extend the ranges as needed. And you can place the lookup table in another sheet. If you use Cut and Paste to move the table, Excel will automatically adjust the formula.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Looking up specific results from a table (Exce

    BTW the formula as posted allows you to change the scores to something else. If they will always be 1, 0, -1, ..., you can simplify it to

    =2-MATCH(B7,OFFSET($I$6:$M$6,MATCH(A7,$H$7:$H$13),0))

  7. #7
    New Lounger
    Join Date
    Feb 2007
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Looking up specific results from a table (Exce

    I've tried to make the data table bigger but now it doesnt work.
    It worked fine when I moved the same size data table to sheet 2, but the moment i made it larger it stoppped working.

    I attach a version with the new data entered, and the score returned on sheet 1, and the comparison data hidden on sheet 2. In terms of entering the formula I just altered the appropriate section of the formula e.g from A2:A8 i extended to A4:A70 so that it referred to the new chart, but im not sure what has caused the problem.

    Any help would be appreciated
    Attached Files Attached Files

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Looking up specific results from a table (Exce

    The problem is not that you have made the table bigger. In the original example, the comparison data increased from left to right in each row. In the present workbook, the comparison data decrease from left to right. You must specify an extra argument in the MATCH function to take this into account:

    =INDEX(Sheet2!$B$2:$F$2,MATCH(B3,OFFSET(Sheet2!$B$ 3:$F$3,MATCH(B2,Sheet2!$A$4:$A$70),0),-1))

    The -1 was added, it specifies that you want to look for the smallest value larger than or equal to the lookup value instead of the largest value smaller than or equal to the lookup value.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •