Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

For example, the column headings of the matrix are compa-ratio ranges, 0-.90, .91-1.05, 1.06-2, etc. and the row headings are score ranges, 0-2, 2.01-2.50, 2.51-3, etc. If your you compa-ratio is between 0-.90 & your score is between 2.51-3, then your increase 6%.

What formula can I use to lookup the compa-ratio value and the score value to yield the appropriate increase % from the matrix?

share|improve this question

1 Answer 1

You can use a combination of VLOOKUP and MATCH

By example:

    A    B   C   D
1     |  0  10  20 
    --|------------   
2   0 |  1   4   7
3   1 |  2   5   8
4   2 |  3   6   9

Cell A6 = 15
Cell B6 = 1.5
Cell C6 = =VLOOKUP(B6,$A$2:$D$4,MATCH(A6,$A$1:$D$1,1),1) Result = 5

How it works

  1. VLOOKUP finds finds value in column A, using approximate match (returns row of largest value less than or equal to lookup value)
  2. MATCH finds finds value in Row 1, using approximate match (returns row of largest value less than or equal to lookup value)
  3. MATCH is used to indicate to VLOOKUP which column to return
share|improve this answer
    
Thank you, Chris. That is exactly what I needed. –  Debi May 13 '12 at 22:28

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.