vlookup part of string...

[milindkeer]

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!

 

[Roger Govier]

Hi

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

 

[Pete_UK]

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

 

[milindkeer]

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










- 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

 

[Pete_UK]

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



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

 

[milindkeer]

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),INDEX($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

 

[Pete_UK]

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