help with lookup and/or substring processing.

[markah]

All.

I have two lists of domain/host names.

Approved
google.com
state.gov
yahoo.com
whitehouse.gov
slashdot.org

And a list of domains/hosts that were scaped from a logfile.

news.google.com
travel.state.gov
www.google.com
www.dpreview.com
www.fark.com
etc.

What I need to do is calculate a cell value that indicates the domain from list #1 that would pass the item in list #2 or use the work "Fail"

new.google.com google.com
travel.state.gov state.gov
www.google.com Fail
www.fark.com Fail

etc.

I have copied the following array formula from a previous thread.
=Lookup(count(search(APPROVED,A1)),{0,1},{"BAD","GOOD"})
That produces the following result:

new.google.com GOOD
travel.state.gov GOOD
www.google.com BAD
www.fark.com BAD

Can anybody help.

Thanks

Mark

[Domenic]

It's not clear to me what it is you're after. Can you be more specific and provide more details?

All.

I have two lists of domain/host names.

Approved
google.com
state.gov
yahoo.com
whitehouse.gov
slashdot.org

And a list of domains/hosts that were scaped from a logfile.

news.google.com
travel.state.gov
www.google.com
www.dpreview.com
www.fark.com
etc.

What I need to do is calculate a cell value that indicates the domain from list #1 that would pass the item in list #2 or use the work "Fail"

new.google.com google.com
travel.state.gov state.gov
www.google.com Fail
www.fark.com Fail

etc.

I have copied the following array formula from a previous thread.
=Lookup(count(search(APPROVED,A1)),{0,1},{"BAD","GOOD"})
That produces the following result:

new.google.com GOOD
travel.state.gov GOOD
www.google.com BAD
www.fark.com BAD

Can anybody help.

Thanks

Mark

[markah]

I am attempting to create a formula that populates a cell with the domain from the "APPROVED" list of domains (a long column of domain names) for a second column of host/domain names in a second list that were extracted from a logfile.

www.google.com would calculate google.com
news.google.com would calculate google.com
www.fark.com would calculate bad.

I can tell (GOOD/BAD or True/False) if www.google.com would calculate against google.com. But I need the value.

Does this help? (Am I less vague?)

Mark

[Domenic]

Assuming that Column A contains List #1 (approved domain names), and Column B contains List #2...

C1, copied down:

=INDEX($A$1:$A$5,MATCH(TRUE,ISNUMBER(MATCH("*"&$A$1:$A$5&"*",B1,0)),0))

OR

=IF(ISNA(MATCH(TRUE,ISNUMBER(MATCH("*"&$A$1:$A$5&"*",B1,0)),0)),"Bad",INDEX($A$1:$A$5,MATCH(TRUE,ISNUMBER(MATCH("*"&$A$1:$A$5&"*",B1,0)),0)))

Both these formulas need to be confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

I am attempting to create a formula that populates a cell with the domain from the "APPROVED" list of domains (a long column of domain names) for a second column of host/domain names in a second list that were extracted from a logfile.

www.google.com would calculate google.com
news.google.com would calculate google.com
www.fark.com would calculate bad.

I can tell (GOOD/BAD or True/False) if www.google.com would calculate against google.com. But I need the value.

Does this help? (Am I less vague?)

Mark

[markah]

Works perfect.
Thanks for the Help.

Mark