IFS function not returned the desired results

[markmcc414]

I am currently using Excel 2016 for Mac. That said, I am all to painfully aware of some of the 'missing' functionality in the 2016 version for Mac that is currently available in the 2016 for version for Windows. That said, I am attempting to created a nested IF or IFS statement to parse a specific string to identify the specific browser + version (ie. Chrome, IE, Firefox, etc.) the cell is referencing. I am pretty sure I have created the statement correctly but it will not return the expected results. When I run each individual IFS statment in it's own cell, the expected result is populated.

Below is the current IFS function I have created:

NOTE: Let me start by saying I am sure there is a better way to do this but after much research and playing around this is what I landed on.

=IFERROR(IFS(MID(A2,FIND("Chrome",A2)*1,6)="Chrome",SUBSTITUTE(MID(A2,FIND("Gecko",A2)+7,9),"/"," "),TRUE,0)
IFS(MID(A2,FIND("Firefox",A2)*1,7)="Firefox",SUBSTITUTE((MID(A2,FIND("Firefox",A2)*1,10)),"/"," "),TRUE,0)
IFS(MID(A2,FIND("CriOS",A2)*1,5)="CriOS",SUBSTITUTE(MID(A2,FIND("Gecko",A2)+7,10),"CriOS/","Chrome Mobile IOS "),TRUE,0)
IFS(MID(A2,FIND(" Version",A2)*1,8)=" Version",SUBSTITUTE(MID(A2,FIND("Gecko",A2)+7,12),"Version/","Safari "),TRUE,0)

IFS(MID(A2,FIND("Trident",A2)*1,7)="Trident",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE((MID(A2,FIND("Trident",A2)*1,44)),")",""),"rv:"," "),"Trident/7.0","IE ")," like Gecko",""),";",""),"Trident/5.0","IE 9.0"),"IE8Mercury ",""),"ASU2JS ",""),"Touch ",""),TRUE,0),0)​

NOTE: I am pretty confident the above function is correct because If I were to enter the above Function into B1 in my spreadsheetand Excel saves it to the cell

Below is some sample rows of data that the function is referencing:

[TABLE="width: 936"]
<tbody>[TR]
[TD]Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/65.0.3325.181 Safari/537.36[/TD]
[/TR]
[TR]
[TD]Mozilla/5.0 (Macintosh; Intel Mac OS X 10_13_3) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/65.0.3325.162 Safari/537.36[/TD]
[/TR]
[TR]
[TD]Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/65.0.3325.181 Safari/537.36[/TD]
[/TR]
[TR]
[TD]Mozilla/5.0 (Windows NT 6.1; WOW64; Trident/7.0; rv:11.0) like Gecko[/TD]
[/TR]
[TR]
[TD]Mozilla/5.0 (Macintosh; Intel Mac OS X 10_13_2) AppleWebKit/604.4.7 (KHTML, like Gecko) Version/11.0.2 Safari/604.4.7[/TD]
[/TR]
[TR]
[TD]Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/65.0.3325.181 Safari/537.36[/TD]
[/TR]
[TR]
[TD]Mozilla/5.0 (Windows NT 6.1; WOW64; Trident/7.0; rv:11.0) like Gecko[/TD]
[/TR]
[TR]
[TD]Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/65.0.3325.181 Safari/537.36[/TD]
[/TR]
[TR]
[TD]Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/65.0.3325.162 Safari/537.36[/TD]
[/TR]
[TR]
[TD]Mozilla/5.0 (Windows NT 6.1; WOW64; Trident/7.0; rv:11.0) like Gecko[/TD]
[/TR]
[TR]
[TD]Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/65.0.3325.181 Safari/537.36[/TD]
[/TR]
[TR]
[TD]Mozilla/5.0 (Windows NT 6.3; WOW64; Trident/7.0; ASU2JS; rv:11.0) like Gecko

If one of the IFS calls are True, the Browser + Version # will be populated in the cell (ex. Chrome 65, IE 11.0, Safari 11.0, etc.)

Currently I receive a '0' as the result for ALL rows (ie. due to the IFERROR function) using this function. As stated above, when I run each individual IFS statement individually it works as expected (ie. returning either the Broser + Version or a 0). I have also created a nexted IF with the same calls. In this case, if the first IFS call was a match for the referenced string it would work as expected. But if the first call didn't match, it would fail.

I am currently running each IFS function in a seperate cell in the same row (5 separate cells). If the IFS function is True the version of the browser is populated in the cell. If it is Fasle, a 0 is populated in the cell. In the 6th cell I am using a LOOKUP function to identify which of the 5 cells does not have a 0. I then take that value and populate it into the 6th cell (see below):

=IFERROR(LOOKUP(2,1/(C2:G2<>0),C2:G2),"Unknown")

NOTE: If all of the cells contain a 0, the cell is populated with 'Unknown'.

At this point in time I have exhaused my knowledge and can not figure out how to make this work in a single cell. In order to simplify my workflow, I would much prefer to use an IFS or nested IF function to combine all of the calls into a single cell, which would reduce the overall work I have to do when additional data is added to the sheet.

Thanks in advance for any thoughts and suggestions.
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Scott Huish]

Duplicate post. See https://www.mrexcel.com/forum/excel-questions/1049287-ifs-function-not-returned-desired-results.html