VLOOKUP table_array wildcard

[Gaetan321]

Using VLOOKUP, I'm comparing 2 sheets to check if the host is monitored. I extracted the list of guests in sheet1 and the list of hosts in sheet2

Sheet1
A1
Win-2K8-dev1 (335d5188-2e63-44cc-978a-51ea4c90e24c)
Win-2K8-mail1 (345d5188-2w64-47cf-973n-53ef459he2rx)
..
.


Sheet2
A1
Win-2K8-dev1
Win-2K8-mail1
..
.

Using the formula below in Sheet1:D1 but it doesn't match

=IFERROR(VLOOKUP(A1&"*",'Sheet2'!$A$1:$A$224,1,FALSE),"NOT MONITORED")

I also tried

=IFERROR(VLOOKUP(A1&"*",SEARCH('Sheet2'!$A$1:$A$224,1),FALSE),"NOT MONITORED")

How can I search the table array with a wildcard ?

Thanks
Gaetan

 

[Aladin Akyurek]

In B1 of Sheet1 enter and copy down:

=IF(ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH(Sheet2!$A$1:$A$100,$A1))),"MONITORED","NOT MONITORED")

 

[Gaetan321]

In B1 of Sheet1 enter and copy down:

=IF(ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH(Sheet2!$A$1:$A$100,$A1))),"MONITORED","NOT MONITORED")

Hi Aladin. I'm afraid it doesn't work. The result returns MONITORED no matter what Sheet1:A has in it


[TABLE="width: 451"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Win-2K8-dev1 (335d5188-2e63-44cc-978a-51ea4c90e24c)
[/TD]
[TD]MONITORED[/TD]
[/TR]
[TR]
[TD]Win-2K8-mail1 (345d5188-2w64-47cf-973n-53ef459he2rx)[/TD]
[TD]MONITORED[/TD]
[/TR]
[TR]
[TD]Win432[/TD]
[TD]MONITORED[/TD]
[/TR]
[TR]
[TD="align: right"]654[/TD]
[TD]MONITORED[/TD]
[/TR]
[TR]
[TD]whatever[/TD]
[TD]MONITORED[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]NOT MONITORED[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]NOT MONITORED[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]NOT MONITORED[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Hard to evaluate without seeing some cases where it fails...

Does this fare better?

=IF(ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH("|"Sheet2!$A$1:$A$100&" ","|"&$A1&" "))),"MONITORED","NOT MONITORED")

If not, could you provide some negative cases? Note that the list from Sheet2 should not have blank cells.

 

[Gaetan321]

Hard to evaluate without seeing some cases where it fails...

Does this fare better?

=IF(ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH("|"Sheet2!$A$1:$A$100&" ","|"&$A1&" "))),"MONITORED","NOT MONITORED")

If not, could you provide some negative cases? Note that the list from Sheet2 should not have blank cells.

OK

Sheet1 has a list of guests as below

A1
ICGB-EXCHANGE3
ROCKT-BAK-1 (335d5188-2e63-44cc-978a-51ea4c90e24c)
ROCKT-DB-1 (b91efa14-ca37-4b38-9f5c-842d4698422c)
ROCKT-DB-2 (09103ea6-53f1-459b-84e7-fa9357cc87bb)
ROCKT-DEV-1 (4abb86cc-de46-47ee-b476-0b0a7d61aa28)
ROCKT-DEV-2 (0ce61ad7-e7fb-44e2-a727-acf3cda38e6f)
ROCKT-STORAGE-1 (ff46ec2e-e4c7-4e52-9a8b-abff227f44ec)
ROCKT-WWW-3 (df5c76c4-8afe-4628-b588-14d189d3a229)

Sheet2 has

A1
rockt-db-1
rockt-db-2
rockt-dev-1
rockt-share-1
rockt-storage-1
rockt-www-3
ICGB-EXCHANGE3


The formula I used in Sheet1:B1 is =IFERROR(VLOOKUP(A1&"*",Sheet2!$A$1:$A$100,1,FALSE),"NOT MONITORED")

and it will match the cells from Sheet2 when found but not the ones that don't match exactly.

I'd like to match the machines from Sheet2 like 'rockt-www-3' like the ones in Sheet1 that contains the same name.

Using the formula, it will show the result for IGCB-EXCHANGE3 but will show NOT MONITORED for all the rest.

If I use your formula, it will show all results as MONITORED even though it's not true

 

[HackSlash]

Try using MATCH and see if you get better results:

=IFERROR(MATCH(A1&"*",Sheet2!$A$1:$A$100),"NOT MONITORED")

 

[Aladin Akyurek]

Trying to explain a problem by means of a non-working formula is rather misleading...

Looks like:

=IFNA(IF(LOOKUP(9.99999999999999E+307,SEARCH(Sheet2!$A$1:$A$100,$A1),Sheet2!$A$1:$A$100)=$A1,"MONITORED","NOT MONITORED"),"NOT MONOTRED")

Is this in the right ballpark?

If you are not on a 2016 Excel version or beyond, replace IFNA with IFERROR.



<strike></strike>

 

[Gaetan321]

Hi Guys. Sorry. I should have mentioned that we're still on Office 2010. We're due to upgrade to office 2016 soon but not yet.

I tried both and it didn't work. See picture attached.

C1 =IFERROR(VLOOKUP(A2&"*",$B$2:$B$100,1,FALSE),"NOT MONITORED")
D1 =IFERROR(IF(LOOKUP(9.99999999999999E+307,SEARCH($B$2:$B$100,$A2),$A$2:$B$100)=$A2,"MONITORED","NOT MONITORED"),"NOT MONITORED")
E1 =IFERROR(MATCH(A2&"*",$B$2:$B$100),"NOT MONITORED")

 

[Aladin Akyurek]

Would you care to post the rows of A:B in an Excel readable form?

 

[Gaetan321]

Would you care to post the rows of A:B in an Excel readable form?

Hi Aladin

Exported A:B into a csv format

List of names to search in,hosts actually monitored,Monitored or Not
ICGB-DC2,rockt-db-1,
ICGB-DSBIL1,rockt-db-2,
ICGB-DSLIC1,rockt-dev-1,
ICGB-EXCHANGE3,rockt-email-2,
ROCKT-BAK-1 (335d5188-2e63-44cc-978a-51ea4c90e24c),rockt-sec-1,
ROCKT-DB-1 (b91efa14-ca37-4b38-9f5c-842d4698422c),rockt-share-1,
ROCKT-DB-2 (09103ea6-53f1-459b-84e7-fa9357cc87bb),rockt-storage-1,
ROCKT-DEV-1 (4abb86cc-de46-47ee-b476-0b0a7d61aa28),rockt-www-3,
ROCKT-DEV-2 (0ce61ad7-e7fb-44e2-a727-acf3cda38e6f),rockt-rds-1,
ROCKT-EMAIL-2 (91d53523-f1e6-40a6-9db8-35b85de79fcf),rockt-www-2,
ROCKT-SEC-1 (12b1f5a3-2937-4ef9-a8f5-e20c9cc14b4c),ICGB-EXCHANGE3,
ROCKT-SHARE-1 (7095f098-7c01-4002-a5c3-da83044cbfe1),ICGB-DSBIL1,
ROCKT-SPAM-1 (f9839642-6a48-47e7-9e8a-08ee1ef05833),ICGB-DC2,
ROCKT-STORAGE-1 (ff46ec2e-e4c7-4e52-9a8b-abff227f44ec),ICGB-DSLIC1,
ROCKT-WWW-3 (df5c76c4-8afe-4628-b588-14d189d3a229),,

Thanks a mil for the help
Gaetan