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!

Hi

I entered Employee number in E1 then used
=IF(E1="","",INDEX(B:B,MATCH("*"&E1&"*",A:A,0)))

--
Regards
Roger Govier

wrote in message
...
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!

You can use wildcards with VLOOKUP, so you could have:

=VLOOKUP("*"&C1&"*",A$2:B$5,2,0)

where C1 contains 994403.

Note that the table entries where there is a single employee number
(eg 994502) need to be text values in order to get an exact match.

Hope this helps.

Pete

On Oct 23, 12:46*pm, wrote:
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!

On 23 Oct, 13:31, Pete_UK wrote:
You can use wildcards withVLOOKUP, so you could have:

=VLOOKUP("*"&C1&"*",A$2:B$5,2,0)

where C1 contains 994403.

Note that the table entries where there is a single employee number
(eg 994502) need to be text values in order to get an exact match.

Hope this helps.

Pete

On Oct 23, 12:46*pm, wrote:



Hi All

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

In above example hpw tovlookuppartofstring?
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 ivlookup"994403" then it should return
London

Can I do this?

vlookupfunction 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!- Hide quoted text -

- Show quoted text -


thanks Roger & Pete for the prompt reply.

It worked!! but as pointed out by Pete the single employee value
should be TEXT now how can i convert it to Text??

I tried TEXT function... but dont really understand wat shout I keep
'format text' parameter??

=TEXT(C1, ???)

or is there any other function to convert number to text?

Thanks again!!
Milind

The simplest way is:

=A1&""

in a helper column and copy down.

Then highlight the column with this formula, click <copy then Edit | Paste
Special | Values (check) | OK then <Esc, which will fix the values. Then
you can copy these and paste over the original values.

Hope this helps.

Pete


wrote in message
...

thanks Roger & Pete for the prompt reply.

It worked!! but as pointed out by Pete the single employee value
should be TEXT now how can i convert it to Text??

I tried TEXT function... but dont really understand wat shout I keep
'format text' parameter??

=TEXT(C1, ???)

or is there any other function to convert number to text?

Thanks again!!
Milind

Hi Pete

Thanks!!

But instead creating 1 more column I used to following formula...

= IF(ISERROR(INDEX($F$11:$F$20,MATCH("*"&A11&"*",$E$ 11:$E$20,0),1)),
INDEX($F$11:$F$20,MATCH(A11,$E$11:$E$20,0),1),INDE X($F$11:$F
$20,MATCH("*"&A11&"*",$E$11:$E$20,0),1))

Which is returning appropriate result.

Thanks a lot for all your inputs... you rock!!

--milind

There are always several ways of doing things in Excel - I'm glad you
found a way that works for you, and thanks for feeding back.

The use of a helper column that I suggested earlier is only temporary
- once you fix the values and then paste them back to the original
column, you can delete the helper column.

Pete

On Oct 23, 4:45*pm, wrote:
Hi Pete

Thanks!!

But instead creating 1 more column I used to following formula...

= IF(ISERROR(INDEX($F$11:$F$20,MATCH("*"&A11&"*",$E$ 11:$E$20,0),1)),
INDEX($F$11:$F$20,MATCH(A11,$E$11:$E$20,0),1),INDE X($F$11:$F
$20,MATCH("*"&A11&"*",$E$11:$E$20,0),1))

Which is returning appropriate result.

Thanks a lot for all your inputs... you rock!!

--milind