# Newbie trying to use VBA if/and function to logically compare two values and provide the corresponding cell value in a matrix

#### TheThinker

##### New Member
Hey all and thanks in advance for helping me with this. I'm currently working with Excel 2007 on WinXP Pro SP3. The file was originally created in Excel 2003, so it's saved as .XLS.

This is kind of difficult to explain, but I have two columns of calculated values (G and I) that I'm trying to compare to values in a matrix on another sheet so that a value within the cell at a particular point in the matrix can be selected. Basically, it's a risk response matrix and the values in G and I are impact and likelihood, respectively. You end up with a formula which, in English, appears like:

If G is less than a certain value on the Y axis of the matrix, and I is less than a certain value on the X axis of the matrix then select the value at the intersecting point.

I've gotten it to the point that it seems to pull some values, but they aren't correct at all.

Here's the code I'm working with:

Code:
``````Function rValue(pval As String) As String

If pval = " " Then
rValue = " "

ElseIf pval = 0 Then
rValue = " "

ElseIf pval < Range("Assumptions!E12").Value And Range("I" & pval + 0).Value < Range("Assumptions!O7").Value Then
rValue = Range("Assumptions!N12").Value

ElseIf pval < Range("Assumptions!E12").Value And Range("I" & pval + 0).Value < Range("Assumptions!M7").Value Then
rValue = Range("Assumptions!L12").Value

ElseIf pval < Range("Assumptions!E12").Value And Range("I" & pval + 0).Value < Range("Assumptions!K7").Value Then
rValue = Range("Assumptions!J12").Value

ElseIf pval < Range("Assumptions!E12").Value And Range("I" & pval + 0).Value < Range("Assumptions!I7").Value Then
rValue = Range("Assumptions!H12").Value

ElseIf pval < Range("Assumptions!E12").Value And Range("I" & pval + 0).Value > Range("Assumptions!G7").Value Then
rValue = Range("Assumptions!F12").Value

ElseIf pval < Range("Assumptions!E11").Value And Range("I" & pval + 0).Value < Range("Assumptions!O7") Then
rValue = Range("Assumptions!N11").Value

ElseIf pval < Range("Assumptions!E11").Value And Range("I" & pval + 0).Value < Range("Assumptions!M7").Value Then
rValue = Range("Assumptions!L11").Value

ElseIf pval < Range("Assumptions!E11").Value And Range("I" & pval + 0).Value < Range("Assumptions!K7").Value Then
rValue = Range("Assumptions!J11").Value

ElseIf pval < Range("assumptions!E11").Value And Range("I" & pval + 0).Value < Range("Assumptions!I7").Value Then
rValue = Range("Assumptions!H11").Value

ElseIf pval < Range("Assumptions!E11").Value And Range("I" & pval + 0).Value > Range("Assumptions!G7").Value Then
rValue = Range("Assumptions!F11").Value

ElseIf pval < Range("Assumptions!E10").Value And Range("I" & pval + 0).Value < Range("Assumptions!O7").Value Then
rValue = Range("Assumptions!N10").Value

ElseIf pval < Range("Assumptions!E10").Value And Range("I" & pval + 0).Value < Range("Assumptions!M7").Value Then
rValue = Range("Assumptions!L10").Value

ElseIf pval < Range("Assumptions!E10").Value And Range("I" & pval + 0).Value < Range("Assumptions!K7").Value Then
rValue = Range("Assumptions!J10").Value

ElseIf pval < Range("Assumptions!E10").Value And Range("I" & pval + 0).Value < Range("Assumptions!I7").Value Then
rValue = Range("Assumptions!H10").Value

ElseIf pval < Range("Assumptions!E10").Value And Range("I" & pval + 0).Value > Range("Assumptions!G7").Value Then
rValue = Range("Assumptions!F10").Value

ElseIf pval < Range("Assumptions!E9").Value And Range("I" & pval + 0).Value < Range("Assumptions!O7").Value Then
rValue = Range("Assumptions!N9").Value

ElseIf pval < Range("Assumptions!E9").Value And Range("I" & pval + 0).Value < Range("Assumptions!M7").Value Then
rValue = Range("Assumptions!L9").Value

ElseIf pval < Range("Assumptions!E9").Value And Range("I" & pval + 0).Value < Range("Assumptions!K7").Value Then
rValue = Range("Assumptions!J9").Value

ElseIf pval < Range("Assumptions!E9").Value And Range("I" & pval + 0).Value < Range("Assumptions!I7").Value Then
rValue = Range("Assumptions!H9").Value

ElseIf pval < Range("Assumptions!E9").Value And Range("I" & pval + 0).Value > Range("Assumptions!G7").Value Then
rValue = Range("Assumptions!F9").Value

ElseIf pval > Range("Assumptions!E8").Value And Range("I" & pval + 0).Value < Range("Assumptions!O7").Value Then
rValue = Range("Assumptions!N8").Value

ElseIf pval > Range("Assumptions!E8").Value And Range("I" & pval + 0).Value < Range("Assumptions!M7").Value Then
rValue = Range("Assumptions!L8").Value

ElseIf pval > Range("Assumptions!E8").Value And Range("I" & pval + 0).Value < Range("Assumptions!K7").Value Then
rValue = Range("Assumptions!J8").Value

ElseIf pval > Range("Assumptions!E8").Value And Range("I" & pval + 0).Value < Range("Assumptions!I7").Value Then
rValue = Range("Assumptions!H8").Value

ElseIf pval > Range("Assumptions!E8").Value And Range("I" & pval + 0).Value > Range("Assumptions!G7").Value Then
rValue = Range("Assumptions!F8").Value

Else: rValue = " "

End If
End Function``````
And here's a link to the document:

Last edited:

### Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

#### HalfAce

##### MrExcel MVP
Probably the first thing I would do is go to using Select Case instead of all the If/ElseIfs. (ie.)
Code:
``````Function rValue(pval As String) As String
rValue = " "
Select Case pval
Case Is < Range("Assumptions!E12").Value And Range("I" & pval + 0).Value < Range("Assumptions!O7").Value
rValue = Range("Assumptions!N12").Value
Case Is < Range("Assumptions!E12").Value And Range("I" & pval + 0).Value < Range("Assumptions!M7").Value
rValue = Range("Assumptions!L12").Value
Case Is < Range("Assumptions!E12").Value And Range("I" & pval + 0).Value < Range("Assumptions!K7").Value
rValue = Range("Assumptions!J12").Value
Case Is < Range("Assumptions!E12").Value And Range("I" & pval + 0).Value < Range("Assumptions!I7").Value
rValue = Range("Assumptions!H12").Value
Case Is < Range("Assumptions!E12").Value And Range("I" & pval + 0).Value > Range("Assumptions!G7").Value
rValue = Range("Assumptions!F12").Value
Case Is < Range("Assumptions!E11").Value And Range("I" & pval + 0).Value < Range("Assumptions!O7").Value
rValue = Range("Assumptions!N11").Value
Case Is < Range("Assumptions!E11").Value And Range("I" & pval + 0).Value < Range("Assumptions!M7").Value
rValue = Range("Assumptions!L11").Value
Case Is < Range("Assumptions!E11").Value And Range("I" & pval + 0).Value < Range("Assumptions!K7").Value
rValue = Range("Assumptions!J11").Value
Case Is < Range("assumptions!E11").Value And Range("I" & pval + 0).Value < Range("Assumptions!I7").Value
rValue = Range("Assumptions!H11").Value
Case Is < Range("Assumptions!E11").Value And Range("I" & pval + 0).Value > Range("Assumptions!G7").Value
rValue = Range("Assumptions!F11").Value
Case Is < Range("Assumptions!E10").Value And Range("I" & pval + 0).Value < Range("Assumptions!O7").Value
rValue = Range("Assumptions!N10").Value
Case Is < Range("Assumptions!E10").Value And Range("I" & pval + 0).Value < Range("Assumptions!M7").Value
rValue = Range("Assumptions!L10").Value
Case Is < Range("Assumptions!E10").Value And Range("I" & pval + 0).Value < Range("Assumptions!K7").Value
rValue = Range("Assumptions!J10").Value
Case Is < Range("Assumptions!E10").Value And Range("I" & pval + 0).Value < Range("Assumptions!I7").Value
rValue = Range("Assumptions!H10").Value
Case Is < Range("Assumptions!E10").Value And Range("I" & pval + 0).Value > Range("Assumptions!G7").Value
rValue = Range("Assumptions!F10").Value
Case Is < Range("Assumptions!E9").Value And Range("I" & pval + 0).Value < Range("Assumptions!O7").Value
rValue = Range("Assumptions!N9").Value
Case Is < Range("Assumptions!E9").Value And Range("I" & pval + 0).Value < Range("Assumptions!M7").Value
rValue = Range("Assumptions!L9").Value
Case Is < Range("Assumptions!E9").Value And Range("I" & pval + 0).Value < Range("Assumptions!K7").Value
rValue = Range("Assumptions!J9").Value
Case Is < Range("Assumptions!E9").Value And Range("I" & pval + 0).Value < Range("Assumptions!I7").Value
rValue = Range("Assumptions!H9").Value
Case Is < Range("Assumptions!E9").Value And Range("I" & pval + 0).Value > Range("Assumptions!G7").Value
rValue = Range("Assumptions!F9").Value
Case Is > Range("Assumptions!E8").Value And Range("I" & pval + 0).Value < Range("Assumptions!O7").Value
rValue = Range("Assumptions!N8").Value
Case Is > Range("Assumptions!E8").Value And Range("I" & pval + 0).Value < Range("Assumptions!M7").Value
rValue = Range("Assumptions!L8").Value
Case Is > Range("Assumptions!E8").Value And Range("I" & pval + 0).Value < Range("Assumptions!K7").Value
rValue = Range("Assumptions!J8").Value
Case Is > Range("Assumptions!E8").Value And Range("I" & pval + 0).Value < Range("Assumptions!I7").Value
rValue = Range("Assumptions!H8").Value
Case Is > Range("Assumptions!E8").Value And Range("I" & pval + 0).Value > Range("Assumptions!G7").Value
rValue = Range("Assumptions!F8").Value
End Select
End Function``````
(And you'll want to double check to make sure I got them all.)

That said,
I've gotten it to the point that it seems to pull some values, but they aren't correct at all.
How close is your original code getting you? (If it's giving the wrong results, then can you see any pattern to what it's doing?)

Last edited:

#### TheThinker

##### New Member
Thanks for the reply.

I've tried to troubleshoot by numbering each case (from 1 to 25) rather than having it return the text values in the matrix, but I've made some substantial edits since the last time I've done that. Now it seems as though it's only pulling values from the first column on the Y axis, so it's possible that the AND statement isn't working the way I want.

I'll try with Select Case and see where that gets me.

Edit: Just tried the code you provided with the same exact results.

Last edited:

#### HalfAce

##### MrExcel MVP
the same exact results
Same results you were getting in the beginning?, or at the end where it only returns values from the first column on the Y axis?

#### TheThinker

##### New Member
Same results as I was getting with the if/and code, returning only the first values on the Y axis.

Replies
14
Views
395
Replies
4
Views
251
Replies
22
Views
608
Replies
1
Views
251
Replies
3
Views
166

### Forum statistics

1,176,402
Messages
5,902,855
Members
435,001
Latest member
ahsanali32 ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?    Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back