Partial Vlookup formula

[MrBramme]

Hi,


I'm looking for a way to do a vlookup for a partial of the text.


example lookupdata:

[pre]

COL A    |ColB
Store 1  |Belgium
Store 2  |UK
Store 3  |Netherlands
so then i'd like to place a vlookup in the data below to find the store location (col B)

COL A                 |ColB
XXXXX Store 1 XXXXXX  |Belgium
XX Store 1 XXXXX      |Belgium
XXXXXXXXX Store 3 XXX |Netherlands

[/pre]
So there's a portion of text before and after the words "Store 1".


I have a rather complex user formula using match() and find() and some more goodies, but it's slow ...

 

[Luke M]

Assuming lookup table is in A1:B10, and new list starts in A11, array formula is:

=INDEX($B$1:$B$10,MATCH(TRUE,ISNUMBER(SEARCH($A$1:$A$10,A11)),0))


Remember to confirm using Ctrl+Shift+Enter, not just Enter.


If we were going the other way, we could use VLOOKUP with wildcards, but since we have to find a specific value within the cell, it's a little trickier.

 

[Absolution]

Or you could use:


=VLOOKUP(CONCATENATE("*",C27,"*"),B22:C24,2,FALSE)


Where C27 is the cell containing the Store you want to lookup, and B22:C24 is the table with Col A & B / Store & Location.

 

[SirJB7]

Hi, MrBramme!

Or following oldchippy's minimalistic spirit you might change Absolution's formula to:

=VLOOKUP("*"&C27&"*",B22:C24,2,FALSE)

That's about 13 character less.

Regards!

 

[MrBramme]

Sorry for the late reply. I tried the 3 approaches, but I think I'm doing something wrong, It's not working for me


please note, I'm using ; in my formula's rather than , ....


You guys mind on taking a look again? here's an example file, since I'm sure that'll be much more efficient


https://www.dropbox.com/s/wwku3thnry4pqm9/Partial%20Vlookup.xlsx

 

[NARAYANK991]

Hi ,


Try this :


=INDEX($B$2:$B$4,SMALL(IF(ISNUMBER(SEARCH("*"&$A$2:$A$4&"*",E2)),ROW($A$2:$A$4)-MIN(ROW($A$2:$A$4))+1),ROW($A$1)))


entered as an array formula , using CTRL SHIFT ENTER. Copy it down.


I have used the comma , since that is my version's separator ; please use what ever is appropriate to your version.


Narayan

 

[Shaun]

Hi MrBramme


You could try:

=VLOOKUP(MID(E2,FIND("Store",E2),7),$A$1:$B$4,2,FALSE)

Cheers


Shaun