Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .