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

1. ## 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. ## 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>

3. ## 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. ## 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.

5. ## 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
•