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]

Thanks for the Excel readable post.

In C2 enter and copy down:

=IFERROR(IF(LOOKUP(9.99999999999999E+307,SEARCH($B$2:$B$15,$A2),$B$2:$B$15)=$A2,LOOKUP(9.99999999999999E+307,SEARCH($B$2:$B$15,$A2),$B$2:$B$15),"NOT MONITORED"),"NOT MONITORED")

 

[Gaetan321]

Thanks for the Excel readable post.

In C2 enter and copy down:

=IFERROR(IF(LOOKUP(9.99999999999999E+307,SEARCH($B$2:$B$15,$A2),$B$2:$B$15)=$A2,LOOKUP(9.99999999999999E+307,SEARCH($B$2:$B$15,$A2),$B$2:$B$15),"NOT MONITORED"),"NOT MONITORED")

I copy/pasted your formula but it still doesn't work.

B6 doesn't match A12 and so on. It is monitored but shows as not monitored. See screen capture

 

[Aladin Akyurek]

I think your definition of what is "monitored" and what is not monitored is not clear.

Try to post what result must obtain in column C for each value in column A.

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][/tr][tr][td]

1​

[/td][td]List of names to search in[/td][td]hosts actually monitored[/td][td]Monitored or Not[/td][/tr]
[tr][td]

2​

[/td][td]ICGB-DC2[/td][td]rockt-db-1[/td][td][/td][/tr]
[tr][td]

3​

[/td][td]ICGB-DSBIL1[/td][td]rockt-db-2[/td][td][/td][/tr]
[tr][td]

4​

[/td][td]ICGB-DSLIC1[/td][td]rockt-dev-1[/td][td][/td][/tr]
[tr][td]

5​

[/td][td]ICGB-EXCHANGE3[/td][td]rockt-email-2[/td][td][/td][/tr]
[tr][td]

6​

[/td][td]ROCKT-BAK-1 (335d5188-2e63-44cc-978a-51ea4c90e24c)[/td][td]rockt-sec-1[/td][td][/td][/tr]
[tr][td]

7​

[/td][td]ROCKT-DB-1 (b91efa14-ca37-4b38-9f5c-842d4698422c)[/td][td]rockt-share-1[/td][td][/td][/tr]
[tr][td]

8​

[/td][td]ROCKT-DB-2 (09103ea6-53f1-459b-84e7-fa9357cc87bb)[/td][td]rockt-storage-1[/td][td][/td][/tr]
[tr][td]

9​

[/td][td]ROCKT-DEV-1 (4abb86cc-de46-47ee-b476-0b0a7d61aa28)[/td][td]rockt-www-3[/td][td][/td][/tr]
[tr][td]

10​

[/td][td]ROCKT-DEV-2 (0ce61ad7-e7fb-44e2-a727-acf3cda38e6f)[/td][td]rockt-rds-1[/td][td][/td][/tr]
[tr][td]

11​

[/td][td]ROCKT-EMAIL-2 (91d53523-f1e6-40a6-9db8-35b85de79fcf)[/td][td]rockt-www-2[/td][td][/td][/tr]
[tr][td]

12​

[/td][td]ROCKT-SEC-1 (12b1f5a3-2937-4ef9-a8f5-e20c9cc14b4c)[/td][td]ICGB-EXCHANGE3[/td][td][/td][/tr]
[tr][td]

13​

[/td][td]ROCKT-SHARE-1 (7095f098-7c01-4002-a5c3-da83044cbfe1)[/td][td]ICGB-DSBIL1[/td][td][/td][/tr]
[tr][td]

14​

[/td][td]ROCKT-SPAM-1 (f9839642-6a48-47e7-9e8a-08ee1ef05833)[/td][td]ICGB-DC2[/td][td][/td][/tr]
[tr][td]

15​

[/td][td]ROCKT-STORAGE-1 (ff46ec2e-e4c7-4e52-9a8b-abff227f44ec)[/td][td]ICGB-DSLIC1[/td][td][/td][/tr]
[tr][td]

16​

[/td][td]ROCKT-WWW-3 (df5c76c4-8afe-4628-b588-14d189d3a229)[/td][td][/td][td][/td][/tr]
[/table]

 

[Gaetan321]

I think your definition of what is "monitored" and what is not monitored is not clear.

Try to post what result must obtain in column C for each value in column A.

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]List of names to search in[/TD]
[TD]hosts actually monitored[/TD]
[TD]Monitored or Not
[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]ICGB-DC2
[/TD]
[TD]rockt-db-1[/TD]
[TD]ICGB-DC2
[/TD]
[/TR]
[TR]
[TD]

3
​

[/TD]
[TD]ICGB-DSBIL1
[/TD]
[TD]rockt-db-2
[/TD]
[TD]ICGB-DSBIL1
[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]ICGB-DSLIC1
[/TD]
[TD]rockt-dev-1[/TD]
[TD]ICGB-DSLIC1
[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]ICGB-EXCHANGE3
[/TD]
[TD]rockt-email-2[/TD]
[TD]ICGB-EXCHANGE3
[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]ROCKT-BAK-1 (335d5188-2e63-44cc-978a-51ea4c90e24c)
[/TD]
[TD]rockt-sec-1
[/TD]
[TD]NOT MONITORED
[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]ROCKT-DB-1 (b91efa14-ca37-4b38-9f5c-842d4698422c)[/TD]
[TD]rockt-share-1[/TD]
[TD]NOT MONITORED
[/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]ROCKT-DB-2 (09103ea6-53f1-459b-84e7-fa9357cc87bb)
[/TD]
[TD]rockt-storage-1[/TD]
[TD]ROCKT-DB-2 (09103ea6-53f1-459b-84e7-fa9357cc87bb)
[/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]ROCKT-DEV-1 (4abb86cc-de46-47ee-b476-0b0a7d61aa28)
[/TD]
[TD]rockt-www-3[/TD]
[TD]ROCKT-DEV-1 (4abb86cc-de46-47ee-b476-0b0a7d61aa28)
[/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]ROCKT-DEV-2 (0ce61ad7-e7fb-44e2-a727-acf3cda38e6f)[/TD]
[TD]rockt-rds-1[/TD]
[TD]NOT MONITORED
[/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD]ROCKT-EMAIL-2 (91d53523-f1e6-40a6-9db8-35b85de79fcf)
[/TD]
[TD]rockt-www-2[/TD]
[TD]ROCKT-EMAIL-2 (91d53523-f1e6-40a6-9db8-35b85de79fcf)
[/TD]
[/TR]
[TR]
[TD]

12​

[/TD]
[TD]ROCKT-SEC-1 (12b1f5a3-2937-4ef9-a8f5-e20c9cc14b4c)
[/TD]
[TD]ICGB-EXCHANGE3[/TD]
[TD]ROCKT-SEC-1 (12b1f5a3-2937-4ef9-a8f5-e20c9cc14b4c)
[/TD]
[/TR]
[TR]
[TD]

13​

[/TD]
[TD]ROCKT-SHARE-1 (7095f098-7c01-4002-a5c3-da83044cbfe1)
[/TD]
[TD]ICGB-DSBIL1[/TD]
[TD]ROCKT-SHARE-1 (7095f098-7c01-4002-a5c3-da83044cbfe1)
[/TD]
[/TR]
[TR]
[TD]

14​

[/TD]
[TD]ROCKT-SPAM-1 (f9839642-6a48-47e7-9e8a-08ee1ef05833)[/TD]
[TD]ICGB-DC2[/TD]
[TD]NOT MONITORED
[/TD]
[/TR]
[TR]
[TD]

15​

[/TD]
[TD]ROCKT-STORAGE-1 (ff46ec2e-e4c7-4e52-9a8b-abff227f44ec)
[/TD]
[TD]ICGB-DSLIC1[/TD]
[TD]ROCKT-STORAGE-1 (ff46ec2e-e4c7-4e52-9a8b-abff227f44ec)
[/TD]
[/TR]
[TR]
[TD]

16​

[/TD]
[TD]ROCKT-WWW-3 (df5c76c4-8afe-4628-b588-14d189d3a229)
[/TD]
[TD][/TD]
[TD]ROCKT-WWW-3 (df5c76c4-8afe-4628-b588-14d189d3a229)
[/TD]
[/TR]
</tbody>[/TABLE]

This is what I'm trying to output. B3 partially matches A8 and outputs the hostname in C8. However, B6 isn't contained in any A column and therefore isn't monitored

 

[Aladin Akyurek]

In C2 enter and copy down:

=IF(ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH($B$2:$B$15,$A2))),$A2,"NOT MONITORED")

 

[Gaetan321]

In C2 enter and copy down:

=IF(ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH($B$2:$B$15,$A2))),$A2,"NOT MONITORED")

Woohoo !! Thanks for the persistence.

 

[Aladin Akyurek]

Woohoo !! Thanks for the persistence.

You are welcome.