Multiple IF arguments

Solved
Report
-
 Tim -
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

Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
490
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

Glad we were able to help! Love us? Write us a review! Rate CCM

CCM 2821 users have said thank you to us this month

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