Multiple If And Statements

[[email protected]]

I have a spreadsheet with either "S" or "P" in Column "B", and one of seven different words (call them "word1", "word2", "word3", "word4", "word5", "word6", "word7") in Column "D"

I am trying to return a specific value in Column "A" based on the value of Column "B" AND Column "D", but obviously have too many conditions for Excel 2003 to do as a function - seems like i need VBA assistance. Anyone have an easily accessible solution?

S&Word1="1", P&Word1="2", S&Word2="3", P&Word2="4", S&Word3="5", P&Word3="6", S&Word4="7", P&Word4="8", S&Word5="9", P&Word5="10".....

Thank you,
Andy

[FDibbins]

Hi and welcome to the forum

I would suggest a quick table with all of the S&Wordx's and their corresponding values
then a simple vlookup to search for the combination you want

If you still have a problem, give me a shout and I will see what I can put together for you

[Prooffreader]

If you want to stick with a function, instead of nested IFs you could use:

=IF(B1="S",CHOOSE(MATCH(D1,{"word1","word2","word3","word4","word5","word6","word7"}),"output1","output2","output3","output4","output5","output6","output7"),IF(B1="P",CHOOSE(MATCH(D1,{"word1","word2","word3","word4","word5","word6","word7"}),"output8","output9","output10","output11","output12","output13","output14")))

output1, 2.... are the values returned for word1, 2... when B1="S", output 8, 9... when B1="P".

I included nothing to validate that B and D are constrained to your values, I could add that if necessary.

[[email protected]]

The function works great - thank you very much!

[[email protected]]

I lied and still need help. The function partially works. Turns out there are 28 possible outcomes - 2 options for Column B - "S" or "P" - and 14 words that can appear in Column D. That said, there are only 10 results that would be put in Column A:
1. "S"&"Word1" = 1.3
2. "S"&"Word2" = 1.3
3. "S"&"Word3" = 1.3
4. "S"&"Word4" = 1.3
5. "S"&"Word5" = 2.3
6. "S"&"Word6" = 2.3
7. "S"&"Word7" = 3.3
8. "S"&"Word8" = 3.3
9. "S"&"Word9" = 3.3
10. "S"&"Word10" = 4.3
11. "S"&"Word11" = 4.3
12. "S"&"Word12" = 4.3
13. "S"&"Word13" = 5.3
14. "S"&"Word14" = 5.3
15. "P"&"Word1" = 1.7
16. "P"&"Word2" = 1.7
17. "P"&"Word3" = 1.7
18. "P"&"Word4" = 1.7
19. "P"&"Word5" = 2.7
20. "P"&"Word6" = 2.7
21. "P"&"Word7" = 3.7
22. "P"&"Word8" = 3.7
23. "P"&"Word9" = 3.7
24. "P"&"Word10" = 4.7
25. "P"&"Word11" = 4.7
26. "P"&"Word12" = 4.7
27. "P"&"Word13" = 5.7
28. "P"&"Word14" = 5.7

Problem with the ChooseMatch function is it does not match up correctly - returns nothing between "2.3" and "3.7", and randomly assigns cells which should have those values to some other value (e.g. "p"&"Word6" will be assigned "4.3" rather than "2.7". Any thoughts?

My full function is below:
=IF(B26="S",CHOOSE(MATCH(D26,{"Word1","Word2","'Word3","Word4","Word5","Word6","Word7","Word8","Word9","Word10","Word11","Word12","Word13","Word14"}),"1.3","1.3","1.3","2.3","2.3","3.3","3.3","3.3","3.3","4.3","4.3","4.3","5.3","5.3"),IF(B26="P",CHOOSE(MATCH(D26,{"Word1","Word2","'Word3","Word4","Word5","Word6","Word7","Word8","Word9","Word10","Word11","Word12","Word13","Word14"}),"1.7","1.7","1.7","2.7","2.7","3.7","3.7","3.7","3.7","4.7","4.7","4.7","5.7","5.7")))

Any help MUCH appreciated!

[[email protected]]

FDibbins - thanks - not terribly familiar with the lookup function. Tried to check it out, but ignorant in that function so likely not grasping how it would do what I want it to. I have a template, into which, beginning at B26, I paste data. I need to output a value into cell A based on the value of B AND value of D. Please see my responses to Proofreader with the function I've been working with and resultant errors, and please let me know if you have any suggestions. I very much appreciate and need all the help I can get!

[FDibbins]

Take a look at the sample below.

	E	F	G
1		S	P
2	Word1	1.3	1.7
3	Word2	1.3	1.7
4	Word3	1.3	1.7
5	Word4	1.3	1.7
6	Word5	2.3	2.7
7	Word6	2.3	2.7
8	Word7	3.3	3.7
9	Word8	3.3	3.7
10	Word9	3.3	3.7
11	Word10	4.3	4.7
12	Word11	4.3	4.7
13	Word12	4.3	4.7
14	Word13	5.3	5.7
15	Word14	5.3	5.7
16
17
18	Code	P
19	Word	word14
20	Value	5.7

If you change the values in F18 and F19, the answer in F20 changes accordingly.
I used this...
=INDEX($E$1:$G$15,MATCH($F$19,$E$1:$E$15,0),MATCH(F18,$E$1:$G$1,0))

(vlookup() works pretty much the same way as the formula I used, but the index/match combo is more flexable)

[martindwilson]

like this vlookup against table

[Prooffreader]

I would go with the vlookup or index/match table, but if you really, really want a single formula and don't care that it's less flexible, here it is:

=CHOOSE(MATCH(B26&D26,{"SWord1","SWord2","SWord3","SWord4","SWord5","SWord6","SWord7","SWord8","SWord9","SWord10","SWord11","SWord12","SWord13","SWord14","PWord1","PWord2","PWord3","PWord4","PWord5","PWord6","PWord7","PWord8","PWord9","PWord10","PWord11","PWord12","PWord13","PWord14"},FALSE),1.3,1.3,1.3,1.3,2.3,2.3,3.3,3.3,3.3,4.3,4.3,4.3,5.3,5.3,1.7,1.7,1.7,1.7,2.7,2.7,3.7,3.7,3.7,4.7,4.7,4.7,5.7,5.7)

The reason you were getting false hits before is that I forgot to put "FALSE" in the MATCH formula earlier in order to force it to use an exact instead of a partial match. Sorry about that.

[[email protected]]

Prooffreader and FDibbins - thank you for your time and effort! Turns out i was missing a ",0" after each set of parameters. Put that in (though no idea what it is for) and all works great! Now just having a formatting problem. After all data is input it is sorted into the "Header Cells" above, which need to retain their pre-sort formatting, which is that any row containing the word "Closed" or "Under Contract" has no borders, while all other cells should have borders. I've tried conditional formatting, but the borderless cells do not migrate with the "Closed' or "Under Contract" cells when sorting - rather, the borderless cells remain where they were. Subsequently, cells that should have borders don't, and the rows containing "Closed" or "Under Contract" have borders. I've attached "Book 3" to show before and after of what I'm shooting for, and have been using a VBA script to color and sort the rows - just can't figure out the borders. Below is the script I've been using, but I'm getting a "Run-time error 438 - Object doesn't support method or property" and when I debug it points to ".TintAndShade=0". Any ideas are much appreciated!

Sub UpdateRowP()

Dim LRow As Integer
Dim LCell As String
Dim LColorCells As String

'start at row 26
LRow = 26

'Update row colors for the first 300 rows
While LRow < 300
LCell = "P" & LRow
'Color will change in columns a-q
LColorCells = "A" & LRow & ":" & "Q" & LRow

Select Case Left(Range(LCell).Value, 6)

'set row color to blue
Case "Brandon"
Range(LColorCells).Font.ColorIndex = 5
End Select
LRow = LRow + 1
Wend

Range("A1").Select

Application.Run "UpdateRowQ"
Application.Run "SortFormat"
Application.Run "setHeights"
ResetBorders

End Sub

Private Sub ResetBorders()

Dim Rng As Range
Dim Styl As Long
Dim R As Long

With ActiveSheet.Columns(3)
For R = 1 To LastRow("C", ActiveSheet)
Set Rng = .Cells(R)
Set Rng = Rng.Resize(1, 2)
If StrComp(.Cells(R).Value, "CLOSED", vbTextCompare) = 0 Or _
StrComp(.Cells(R).Value, "UNDER CONTRACT", vbTextCompare) = 0 Then
SetBorders xlContinuous, Rng
SetBorder xlInsideVertical, xlNone, Rng
Else
SetBorders xlNone, Rng
If R > 1 Then
Set Rng = Rng.Offset(-1, 0)
If Rng.Borders(xlEdgeRight).LineStyle = xlContinuous Then
SetBorder xlEdgeBottom, xlContinuous, Rng
End If
End If
End If
Next R
End With
End Sub

Private Sub SetBorders(ByVal Styl As Long, _
Rng As Range)

Dim Bd As Long

Rng.Borders(xlDiagonalDown).LineStyle = xlNone
Rng.Borders(xlDiagonalUp).LineStyle = xlNone
For Bd = xlEdgeLeft To xlInsideHorizontal
SetBorder Bd, Styl, Rng
Next Bd
End Sub

Private Sub SetBorder(ByVal Bd As Long, _
ByVal Styl As Long, _
Rng As Range)

With Rng.Borders(Bd)
.LineStyle = Styl
If Styl = xlNone Then Exit Sub
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
End Sub

Private Function LastRow(Optional ByVal Col As Variant, _
Optional Ws As Worksheet) As Long
' 0059 V 3.2 Apr 2, 2012

Dim R As Long

If Ws Is Nothing Then Set Ws = ActiveSheet
If VarType(Col) = vbError Then Col = 1
With Ws
R = .Cells(.Rows.Count, Col).End(xlUp).Row
With .Cells(R, Col)
If R = 1 And .Value = vbNullString Then R = 0
LastRow = R + .MergeArea.Rows.Count - 1
End With
End With
End Function