lookup with wildcards

[mtwebbmc]

I have a sheet1 with column a that has streets on it and column b has side of town

column a column b
bill main
bob east
ted west

i have another sheet2 that has a column listed as address i want a column to say the side of town its on

column address column side of town
123 bill st main
89 ted cir west
421 bob lp east

I dont mind if it has to be a vba or a formula the key thing i want to look for is the bill ted or bob to determine what side of town it is.

i've tried index match vlookup but can't figure out a way to use wildcards like *bill* or *ted* or *bob*

thanks for any help

 

[Peter_SSs]

Welcome to the MrExcel board!

I've just done this in one sheet but you should be able to adapt to 2 sheets if it is what you want. Or post back.

Formula copied down.

Excel Workbook
	A	B	C	D	E	F
1	column address	column side of town
2	123 bill st	main			bill	main
3	89 ted cir	west			bob	east
4	421 bob lp	east			ted	west
Town side

 

[mtwebbmc]

Thank you very much that is what i needed.

 

[Peter_SSs]

Great, thanks for letting us know.

 

[Aladin Akyurek]

Without division which is costly...

=LOOKUP(9.99E+307,SEARCH($E$2:$E$4,A2),$F$2:$F$4)

 

[Peter_SSs]

Without division which is costly...

=LOOKUP(9.99E+307,SEARCH($E$2:$E$4,A2),$F$2:$F$4)

Can't argue with the statement, but just pointing out that the quantum does not appear to be great.
With 50,000 rows of data and 10 items in the lookup range the difference was about 2/100 of a second or about 4% on my machine running Win 7 and Excel 2010.

 

[Aladin Akyurek]

Can't argue with the statement, but just pointing out that the quantum does not appear to be great.
With 50,000 rows of data and 10 items in the lookup range the difference was about 2/100 of a second or about 4% on my machine running Win 7 and Excel 2010.

I did hope it would be more. Thanks for profiling, Peter.