# I have a matrix in Excel and I need a formula to look up 2 values within two separate ranges to yield a specific percentage

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?

-

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
-
Thank you, Chris. That is exactly what I needed. – Debi May 13 '12 at 22:28