VLOOKUP to match substring

[Milind Keer]

Hi All

Employee No.......................... Place
99440,994403,994404.............London
99442,994401,994405.............Manchester
994410..................................Leeds
994502..................................York

In above example hpw to vlookup part of string?
Each cell has multiple employee no. but i want to lookup single employee no
and it should return place.

e.g. If in above example if i vlookup "994403" then it should return London

Can I do this?

vlookup function returning appropriate value for '994410' & '994502' but not
the rest!!

I tried FIND, SEARCH functions but these to functions are for single cell
and not for array...

Thanks!

 

[muddan madhu]

suppose col A has employee no. and col B has place,

In D1 has employee no & in E1 put this formula

=INDEX(B1:B5,MATCH("*"&D1&"*",A1:A5,0),0)

 

[TomPl]

That is very good. With a little experimentation I have discovered that this
formula also works:

=VLOOKUP("*" & D1 & "*",$A$1:$B$5,2,FALSE)