Partial text search against an array

[Ucubia]

Hi

I have two worksheets, one that has a list of hostnames and one that is a list of closed platforms

In the Platforms worksheet if I enter the formula =countif(Hostnames!a:a,A2&"*") this will look for the characters "FRED" in the Hostname sheet and in this case return the value of 3

However, I really need to do the reverse, as in if HOSTNAME starts with the characters in the Platform sheet/array, then flag it as "ready for decommission"

Ideas welcome on how best to do it?

Hostnames (Col A) Platforms (Col A) Count (Col B)

FRED FRED 3
FRED123 MARY 2
FRED567
MARY
MARYABC


Many thanks in advance

regards, Ian

 

[shift-del]

Hello Ian

Try this.

=IF(SUM(--ISNUMBER(SEARCH(Platforms!$A$1:$A$2,Hostname!A1))),"ready for decommission","")

 

[RoryA]

If you specifically need to search for items that start with the platform data, then perhaps:

=IFERROR(LOOKUP(2,1/(IFERROR(SEARCH(Platforms!$A$1:$A$2,A1),2)=1),Platforms!$B$1:$B$2),"No match")

 

[Ucubia]

Hello Ian

Try this.

=IF(SUM(--ISNUMBER(SEARCH(Platforms!$A$1:$A$2,Hostname!A1))),"ready for decommission","")

If you specifically need to search for items that start with the platform data, then perhaps:

=IFERROR(LOOKUP(2,1/(IFERROR(SEARCH(Platforms!$A$1:$A$2,A1),2)=1),Platforms!$B$1:$B$2),"No match")

Thank you both - I don't think I really explained the issue well enough.

Basically in one worksheet I have a list of hostnames e.g. Fred, Fred123, Fred456 - the platform is Fred, and Fred123, Fred456 are essentially interfaces upon Fred.

In the other worksheet I just have Platforms in this case Fred

So in the hostnames worksheet, I want to take the hostname from the cell, and then perform a lookup in the platforms worksheet. If the cell in hostname worksheet starts with "Fred" then I can simply say "ready to decommission" and that will apply to Fred, Fred123, Fred456. That's been the challenge.

I would add the in reality hostnames is about 10,000 rows and platforms is about 1,000

But thank you for assisting, much appreciated.

 

[RoryA]

So in what way do those formulas not do what you want?

 

[Ucubia]

Hi Rory,

Thanks for your offer to help, it is literally text searching - i.e. hostname FRED123, does it contain the platform name in another sheet which does indeed have a platform name of FRED.

The problem being hostnames I could literally have FRED123, FRED123456, FRED9999, FREDXXX etc etc. In reality the hostname is comprised of Platformname+Instance. So when the admin guys switch off FRED, it will flag for decommission FRED123, FRED123456, FRED9999, FREDXXX

I have tried a number of ways to match (partially) against the platforms - I can't get it to work,

 

[shift-del]

Taking your example my formula spills out "ready for decommission" for every hostname.
Please explain what result did you expect.

 

[RoryA]

The formulas provided do what you are describing - i.e. search through the list of platform names and find the one that matches the start of the hostname.

 

[Ucubia]

thank you Rory

I will re-attempt

 

[Ucubia]

I have just re-attempted on the spreadsheet and it failed - I took a closer look at the data and found whitespace, so I used TRIM to tidy that up and it worked!

Other searches worked fine before TRIM so assume this formula detects more - I have no idea!

Thank your assistance, and paitence - very much appreciated