Formula: =IF((ISNUMBER(SEARCH("172.29.90.",$A3)))*(ISNUMBER(SEARCH("00-00-85",$F3))),"MF

**dude6571** · 10-24-2017, 05:12 PM

Hi,

I am having issue with formula

=IF((ISNUMBER(SEARCH("172.29.90.",$A3)))*(ISNUMBER(SEARCH("00-00-85",$F3))),"MF"&LEFT([@Location],3),
IF((ISNUMBER(SEARCH("172.29.92.",$A3)))*(ISNUMBER(SEARCH("00-00-85",$F3))),"MF"&LEFT([@Location],3),
IF((ISNUMBER(SEARCH("172.29.84.",$A3)))*(ISNUMBER(SEARCH("00-00-85",$F3))),"MF"&LEFT([@Location],3),
IF((ISNUMBER(SEARCH("172.29.74.13",$A3)))*(ISNUMBER(SEARCH("00-00-85",$F3))),"MF"&LEFT([@Location],8),
IF((ISNUMBER(SEARCH("172.29.80.",$A3)))*(ISNUMBER(SEARCH("00-00-85",$F3))),"MF"&LEFT([@Location],3),
IF((ISNUMBER(SEARCH("172.29.96.",$A3)))*(ISNUMBER(SEARCH("00-00-85",$F3))),"MFICV"),
"Unknown"))))))

Column E is hidden in my spreadsheet - $A3 and $F3 are correct

Error message: You've entered too many arguments for this function.

Any guide would be highly appreciated

Tony

**daddylonglegs** · 10-24-2017, 05:17 PM

I believe you need to take out the closing parenthesis, after "MFICV" so that there's only a comma between that and "Unknown"

**daddylonglegs** · 10-25-2017, 05:46 PM

Originally Posted by daddylonglegs

I believe you need to take out the closing parenthesis, after "MFICV" so that there's only a comma between that and "Unknown"

Did you try my suggestion?

**leelnich** · 10-24-2017, 07:55 PM

Difficult to test without a workbook, but try this more-efficient arrangement:

=IF(ISNUMBER(SEARCH("00-00-85",$F3)),IF(SUM(--(LEFT(A3,10)="172.29."&{"90.","92.","84.","80."})),"MF"&LEFT([@Location],3),IF(LEFT(A3,12)="172.29.74.13","MF"&LEFT([@Location],8),IF(LEFT(A3,10)="172.29.96","MFICV","Unknown"))),"Unknown")

**dude6571** · 10-25-2017, 05:25 PM

Please find attached a workbook

**leelnich** · 10-26-2017, 02:26 AM

@daddylonglegs was correct in post #2 re: parenthesis. My solution also works on sample data. Both included in attached file.

=IF(LEFT([@MACAddressFormat],8)="00-00-85",IF(SUM(--(LEFT(A2,10)="172.29."&{"90.","92.","84.","80."})),"MF"&LEFT([@Location],3),IF(LEFT(A2,12)="172.29.74.13","MF"&LEFT([@Location],8),IF(LEFT(A2,10)="172.29.96.","MFICV","Unknown"))),"Unknown")

**dude6571** · 11-01-2017, 04:40 PM

Thank you guys for your prompt reply.

Highly appreciated

Cheers

**leelnich** · 11-04-2017, 07:36 AM

No problem, happy to help. If concluded, please mark your thread as SOLVED (Thread Tools up top).

...and thank you for the rep! -Lee

Formula: =IF((ISNUMBER(SEARCH("172.29.90.",$A3)))*(ISNUMBER(SEARCH("00-00-85",$F3))),"MF

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Formula: =IF((ISNUMBER(SEARCH("172.29.90.",$A3)))*(ISNUMBER(SEARCH("00-00-85",$F3))),"MF

Re: Formula: =IF((ISNUMBER(SEARCH("172.29.90.",$A3)))*(ISNUMBER(SEARCH("00-00-85",$F3)))

Re: Formula: =IF((ISNUMBER(SEARCH("172.29.90.",$A3)))*(ISNUMBER(SEARCH("00-00-85",$F3)))

Re: Formula: =IF((ISNUMBER(SEARCH("172.29.90.",$A3)))*(ISNUMBER(SEARCH("00-00-85",$F3)))

Re: Formula: =IF((ISNUMBER(SEARCH("172.29.90.",$A3)))*(ISNUMBER(SEARCH("00-00-85",$F3)))

Re: Formula: =IF((ISNUMBER(SEARCH("172.29.90.",$A3)))*(ISNUMBER(SEARCH("00-00-85",$F3)))

Re: Formula: =IF((ISNUMBER(SEARCH("172.29.90.",$A3)))*(ISNUMBER(SEARCH("00-00-85",$F3)))

Re: Formula: =IF((ISNUMBER(SEARCH("172.29.90.",$A3)))*(ISNUMBER(SEARCH("00-00-85",$F3)))

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] VBA code - IF(ISNUMBER(SEARCH("Adobe", $A4)),"Adobe Pro")

7 nested IF statements with a SEARCH added IF(ISNUMBER(SEARCH({"INSUR","AP REFUND"}

[SOLVED] Using =IF(ISNUMBER(SEARCH("SU",BK5)),"Special User" on a range of cells rather than just 1

Required "IF(ISNUMBER(SEARCH" related formula

Alternative to nesting formula? "IF(ISNUMBER(SEARCH"

Cross reference using "isnumber" & "match"

using "isnumber(search" for a range of numbers

Bookmarks

Bookmarks

Posting Permissions