Multiple IF arguments

Solved
Tim - Oct 31, 2021 at 11:30 PM
 Tim - Nov 2, 2021 at 08:24 PM
Hello,

I am trying to get Excel to look at data in two cells and depending on the combination it finds in those cells, record a different TRUE statement. i.e. There are five possible data points in cell 1, and another 5 in cell 2, so 25 combinations in total (sequence is not important). Depending on the pairing, I want a third conditional cell to trigger a different response.

Is this too hard for Excel? It appears to be too hard for me! And I get an error message saying "too many arguments".

Please help - I am not an Excel expert - in case you coudn't tell!

Tim

System Configuration: Macintosh / Chrome 95.0.4638.54

1 reply

TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
515
Nov 2, 2021 at 01:02 PM
Hi Tim,

Nested IF's have been limited to 7. You could use additional cells to put in more IF's, but the best way to get past this limitation is to use a user defined function.

Here is what it looks like:
Function CellCombi(cell1, cell2 As Range) As String
If cell1 = "a" And cell2 = "a" Then CellCombi = "aa"
If cell1 = "b" And cell2 = "a" Then CellCombi = "ba"
If cell1 = "c" And cell2 = "a" Then CellCombi = "ca"
If cell1 = "d" And cell2 = "a" Then CellCombi = "da"
If cell1 = "e" And cell2 = "a" Then CellCombi = "ea"
'etc..

End Function


To use this function press Alt+F11 to open the VBA window. In the top menu > insert > module. Paste the code in the big white field. You can close this window now.

Back in Excel, use the function like this:
=CellCombi(A1,B1)

Let us know if something is unclear.

Best regards,
Trowa
1
Thank you! First time asking anything like this on a forum, and that is an outstanding (and successful) response. Thank you again. TIm
0