Wildcard vlookup return multiple values

[Manikandan Arumugam]

I need a formula with no Macros – here an example of what I’m trying to do.

I have an entries of 10000+. below mentioned the small amount of entries
Workbook A contains:

10101 REUNION PLACE, SUITE 910, SAN ANTONIO, TX, 78216
PO BOX 1012, WINCHESTER, MA, 01890
1166 JAMESTOWN RD, WILLIAMSBURG, VA, 23188
4569 STREET, apartment 801, SAN ANTONIO, TX, 94105
13714 SOUTHERNWOOD CT., CHANTILLY, VA, 20151
4600 SOUTH SYRACUSE STREET, SUITE 500, DENVER, CO, 80237
4568 AVENUE, WINCHESTER, MA, 10022

Workbook 2 contains:

cell | lookup value

A2|SAN ANTONIO, TX
A3|SAN ANTONIO, TX
A4|WINCHESTER, MA
A5|WINCHESTER, MA

when i lookup workbook 2 values with workbook 1 by using wildcard lookup, it returns the first result of matching entry . Is der any way to get the multiple values using vlookup formula.

Formula i used: =vlookup("*",A2,"*",workbook 1 first column array,2,0)

result i got:

cell|lookupvaue
A2|SAN ANTONIO, TX|10101 REUNION PLACE, SUITE 910, SAN ANTONIO, TX, 78216
A3|SAN ANTONIO, TX|10101 REUNION PLACE, SUITE 910, SAN ANTONIO, TX, 78216
A4|WINCHESTER, MA|PO BOX 1012, WINCHESTER, MA, 01890
A5|WINCHESTER, MA|PO BOX 1012, WINCHESTER, MA, 01890

result i want:

A2|SAN ANTONIO, TX|10101 REUNION PLACE, SUITE 910, SAN ANTONIO, TX, 78216
A3|SAN ANTONIO, TX|4569 STREET, apartment 801, SAN ANTONIO, TX, 94105
A4|WINCHESTER, MA|PO BOX 1012, WINCHESTER, MA, 01890
A5|WINCHESTER, MA|4568 AVENUE, WINCHESTER, MA, 10022

Please help me..Thanks in advance

[Pete_UK]

It would help if you attached a sample Excel workbook.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Please note that the Paperclip icon does not work.

Pete

[BillyRaySpivy]

I was able to do the match on sheet 2. Maybe this could be a starting point to hyperlink to the new workbook?

[Manikandan Arumugam]

Thanks for your replies Pete_Uk and BillyRaySpivy

Attached the sample files for your reference Pete_Uk.

Thanks Again. It will be helpful to improve my excel skills.

[Manikandan Arumugam]

Thanks Billy. Already I have tried the formula's which u have shared. but its not meets my output i want.

[Manikandan Arumugam]

Thanks for your replies Pete_Uk.

Attached the sample files for your reference Pete_Uk.

Thanks Again. It will be helpful to improve my excel skills.

[shukla.ankur281190]

Try

C2

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

[Manikandan Arumugam]

Thank You Shukla. Simply Great

[shukla.ankur281190]

Glad it helps you...

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.