Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Mar 2004
    Location
    Winfield, West Virginia, USA
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Lokking for the correct formula to use (Excel 2000)

    I have an Excel work sheet with values down the first column called AHT and another set of values across the top row called Quality scores. I need a formula that will take an individuals AHT score and search down the AHT column to find it. I then need the formula to search across the Quality score row and find that individuals Quality score. There is a value called points at the intersecting point of the AHT and Quality score. I need the formula to get that value. so it can be added to another work sheet. Any help putting this formula together will be greatly appreciated.

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lokking for the correct formula to use (Excel 2000)

    If the individuals AHT score is in C3, and the individuals Quality score is in H3, then the following formula will give the value at the intersecting point:

    <pre>=OFFSET(A15,MATCH(C3,A16:A41,0),MATCH(H3,B13: L13,0))
    </pre>

    Legare Coleman

  3. #3
    New Lounger
    Join Date
    Mar 2004
    Location
    Winfield, West Virginia, USA
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lokking for the correct formula to use (Excel 2000)

    Thanks for the formula. =OFFSET(A15,MATCH(C3,A16:A204,0),MATCH(H3,B13:CW13 ,0)) I changed the ranges to match the ranges of my entire spreadsheet. The formula appears to work some of the times. Other times it is returning a #N/A. Do you have any suggestions?

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lokking for the correct formula to use (Excel 2000)

    You will get a #N/A error if either if the Match functions do not get an exact match. This could be due to rounding errors. There are several possible things you can do depending on how you want to handle the situation. Changing the last parameter to the MATCH functions to a -1. However, that may not select the value you want. Another possibility would be to use the ROUND function to make sure that the lookup values and the values in the lookup arrays are rounded to the same number of decimal places.
    Legare Coleman

  5. #5
    New Lounger
    Join Date
    Mar 2004
    Location
    Winfield, West Virginia, USA
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lokking for the correct formula to use (Excel 2000)

    I really appreciate your help. The formula is working fine. I had to use some rounding commands as you suggested.

Posting Permissions

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