How to vlookup with wild card and return multiple values

[StrekVoyager]

Okay I have a problem with a vlookup using a wild card.

In my example i want to lookup anything like [cert] under the names column. But I also need to see both multiple instances of the same name as well as the other names contain anything like cert.

So right now I am using =vlookup("*"&A2"*",B:B,1,FALSE)

Also is there a way to return anything with brackets and only display the brackets and characters within. The brackets around the name will allways be at the beginning of the name but they may contain a different ammount of characters.

 

[SkipVought]

hi,

Some of us are restricted by company software from accessing certain data on the www.

Please post HERE, some representative data you want to match along with a corresponding lookup value.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[StrekVoyager]

Item to Lookup
[Cert] (A2)

(LOOKUP AREA)
Name Loc Notes
[Cert] =VLOOKUP("*"&$A$2&"*",$A$26:$A$39,1,FALSE)

(LOOKUP ARRAY A26:A39)
Name Loc Notes
[Certa] 1304 1533 Complete
[Certa] 1304 1534 ERROR
[Certa] 1100 1535 Complete
[Cert] 2215 1536 Complete
[Prod] 102 1537 Complete
[K21119] 10009 1538 Complete
[K21119] 10009 1539 Complete
[K21119] 10009 1540 Complete
[K21119] 10009 1541 Pending
[Prod] 104 1542 Complete

 

[SkipVought]

In your lookup table, create a new column that contains the data to the left of the SPACE in column A.
[tt]
=LEFT(A2,FIND(" ",A2)-1)
[/tt]
Use this column as your lookup column.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[StrekVoyager]

=LEFT(A2,FIND(" ",A2)-1)

Okay can you explain what this is doing? Because A2 is now B2 when i insert to the left of A.

 

[StrekVoyager]

Okay so this will allow for 6 variations if i subtract 1 each time. What if I want to return 30 items?

 

[SkipVought]

If you inserted a column to the left (A), then the reference is B2.


So then use the PivotTable wizard to sumarize by your 6.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[kendue]

Have you tried applying an AutoFilter to the column containg your bracketed information?

Click on the "Custom" selection and type "[ce" or "[K", etc. for your criteria to search for. This will list all information that meets your criteria.