How to merge two functions in a single cell

[Rinkojhon]

Hi,

I am trying to merge following formulas in a single cell -

=IF(ISNUMBER(A2) + IFERROR(FIND(".com",A2),0) + IFERROR(FIND(".org",A2),0) + IFERROR(FIND(".net",A2),0) + IFERROR(FIND("www.",A2),0) + IFERROR(FIND("com.",A2),0) + IFERROR(FIND("300x250",A2),0) + IFERROR(FIND("728x90",A2),0) + IFERROR(FIND("/",A2),0) + IFERROR(FIND("_",A2),0) + IFERROR(FIND("Android",A2),0) + IFERROR(FIND("ios",A2),0) + IFERROR(FIND("Application",A2),0) + IFERROR(FIND("android",A2),0) + IFERROR(FIND("Ios",A2),0) + IFERROR(FIND("application",A2),0) + IFERROR(FIND("ANDROID",A2),0) + IFERROR(FIND("IOS",A2),0) + IFERROR(FIND("APPLICATION",A2),0) + IFERROR(FIND("300*250",A2),0) + IFERROR(FIND("728*90",A2),0) + IFERROR(FIND("IPHONE",A2),0) + IFERROR(FIND("Iphone",A2),0) + IFERROR(FIND("iphone",A2),0) + IFERROR(FIND("300",A2),0) + IFERROR(FIND("728",A2),0) + IFERROR(FIND("320",A2),0) + IFERROR(FIND("50",A2),0) + IFERROR(FIND(".COM",A2),0) + IFERROR(FIND(".ORG",A2),0) + IFERROR(FIND(".NET",A2),0) + IFERROR(FIND("WWW.",A2),0) + IFERROR(FIND("COM.",A2),0) + IFERROR(FIND("300x250",A2),0) + IFERROR(FIND("728x90",A2),0) + IFERROR(FIND("/",A2),0) + IFERROR(FIND("_",A2),0) + IFERROR(FIND("aNDROID",A2),0) + IFERROR(FIND("iOS",A2),0) + IFERROR(FIND("aPPLICATION",A2),0)> 0,A2,"")

AND

=IFERROR(VLOOKUP(A2,Applist!$B$2:$C$6085, 2, FALSE), "")

but unable to make it perfectly. Would you please help me to solve it.

Here is my google doc link - https://docs.google.com/spreadsheets...GIn8Ndg-c/edit where I am working now. You can check this to get relevant data.

Best,
Rinko.

[sandy666]

First I would change this long formula to:

Please Login or Register  to view this content.

where temp_rng is a list (named range) of your find_text parameter
but the rest I will try to understand

It would be good to see final result.

EDIT:
you can join two formulas by &, like:

Please Login or Register  to view this content.

but I think this is not what you are looking for

[Rinkojhon]

Hi sandy,

Thank You so much, though I don't have that much knowledge for excel formula. This formula seems totally different formula that I was thinking, Hope it will work.
Thank you again.

best,
RInko.

[sandy666]

Try and ask if you have any question.

[Rinkojhon]

Hi Sandy,

It is working perfectly. Just little more query. is there any way to present those "temp_rng" in a common name ? like - if formula matched 300x250 then it will show "Blocked apps"

best,
Rinko

[sandy666]

Define and use names in formulas

temp_rng is a list (named range) of your:
com.
org.
WWW.
www.
aNDROID
.org
.........
etc.

this is what you are looking for in first part of formula. You are looking "com." in A2 but...
the second part (with VLOOKUP) looking value of A2 in another sheet in B:C and return value from C
this is two different values to lookup and two different results.

or I don't understand

maybe you want:

Please Login or Register  to view this content.

edit:
1. you need to create list of your all find_text parameters
2. if you want use range you should use: e.g. Sheet6!$A$1:$A$2 not only Sheet6!A1:A2
3. read more about named range you can define named range in Google sheet also

1. Open a spreadsheet in Google Sheets.
2. Select the cells you want to name.
3. Click Data > Named ranges. A menu will open on the right.
4. Type the range name you want.
5. To change the range, click Spreadsheet Grid.
6. Select a range in the spreadsheet or type the new range into the text box, then click Ok.
7. Click Done.

Range names:

• Can contain only letters, numbers, and underscores.
• Can't start with a number, or the words "true" or "false."
• Can't contain any spaces or punctuation.
• Must be 1–250 characters.
• Can't be in either A1 or R1C1 syntax. For example, you might get an error if you give your range a name like "A1:B2" or "R1C1:R2C2."

etc.... etc...