Solved: vlookup for part of word with separator

[danovkos]

Hi all,
pls. is it possible to do following with formula?

I want to use vlookup function for column A.
This column can contains values like e.g. ";ad3;dan;red". All in one cell.
This ";" is separator of my values.

How can i do, that formula will look at this value in cell and will use vlookup for each part of value between separators (;)?

I mean
in A1 is value "ad3,dan;red" and in column B will be formula, which result will be the same as i useded vlookup ("ad3";C1:D20;2;0) & vlookup ("dan";C1:D20;2;0) & vlookup ("red";C1:D20;2;0). Now i can not define, where start separator and then where end and then look for next start of separator and where end and....

The worst is, that value in column A can be with 1 separator (;peter) or max 6 (;peter;red;blue;grey;city;dan). So there can be 1 or 6 partial values. :(

Is it possible to do this with formula?
thx a lot

[slamet Harto]

how about
=VLOOKUP("*"&"ad3"&"*"

[danovkos]

thx for try, but
but if i look for this value

"*"&"ad3"&"*"

, it looks always for value "ad3" but it can start with anything and end with enything yes?

but in my cell can be many different values :(. Maybe there will not "ad3" but something else. What does not change is separator.

or did you mean it in other way?

[Bob Phillips]

Try a UDF



Public Function MultiLookup(rng As Range, lookup As Range, col As Long) As String
Dim vecValues
Dim i As Long
Dim tmp As String

vecValues = Split(rng.Value, ";")
For i = LBound(vecValues) To UBound(vecValues)

If vecValues(i) <> "" Then

tmp = tmp & Application.VLookup(vecValues(i), lookup, col, False)
End If
Next i

MultiLookup = tmp
End Function


use like this

=MultiLookup(A1,C1:D20,2)

[danovkos]

as usual...XLD....fantastic...
exactly what i needed

thx a lot mate:thumb :bow: