Match formula help

[KK1966]

Hi,

i have attached the sample file, hope it's clearly the what i want.
looking help by formulas at range b2 to down found match on at col "D" than return the value by "E" to "b".

pleased.
sorry to by limited English

[mbarron]

I'm assuming your serial-# is coming from a value that would be entered in the B column.


=INDEX($B$2:$B$5,MATCH(VLOOKUP("*" & D2 & "*",$A$2:$A$5,1,0),$A$2:$A$5,0))

[KK1966]

I'm assuming your serial-# is coming from a value that would be entered in the B column.


=INDEX($B$2:$B$5,MATCH(VLOOKUP("*" & D2 & "*",$A$2:$A$5,1,0),$A$2:$A$5,0))


Thanks your help,

it was ok, but can't drag down for all, actully, base your formulas to change Vlookup A2 to found the serial value from D2:E5, i have trying but, it error !! do any suggests thanks, thanks,
trying is error below

=INDEX($E$2:$E$5,MATCH(VLOOKUP(A2,"*"&$D$2:$D$5&"*",1,0),$D$2:$D$5,0))

[mbarron]

I got your requirements backwards. I thought the D and E columns were what you wanted as the end product.

If you are able to add an extra column you can do the following:
In the F column use:


=MATCH(VLOOKUP("*" & D2 & "*",$A$2:$A$5,1,0),$A$2:$A$5,0)

Then in the B column use:

=INDEX($E$2:$E$5,VLOOKUP(ROW()-1,$F$2:$F$5,1,0))

[KK1966]

I got your requirements backwards. I thought the D and E columns were what you wanted as the end product.

If you are able to add an extra column you can do the following:
In the F column use:

=MATCH(VLOOKUP("*" & D2 & "*",$A$2:$A$5,1,0),$A$2:$A$5,0)

Then in the B column use:
=INDEX($E$2:$E$5,VLOOKUP(ROW()-1,$F$2:$F$5,1,0))


Thanks very much, but it's was shown #N/A when frequency record!
can it be solve ?

[KK1966]

Can it applied to VBA for if col D match part of col A then col B value = col E?

[KK1966]

Can it applied to VBA for if col D match part of col A then col B value = col E?


i trying to adding VBA code but it nothing as i just newly to try!!

can some one help if what i go wrong ?

Sub test()
Dim LR As Long, i As Long, R As Long
Set objDataRange = Sheet1.UsedRange
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LR
For R = 2 To LR
If Sheet1.Cells("*" &( i, 4)) & "*").Value = Sheet1.Cells(R, 1) Then
Sheet1.Cells(R, 2).Value = Cells(i, 5)
End If
Next R
Next i
MsgBox ("ok")
End Sub

[KK1966]

i trying to adding VBA code but it nothing as i just newly to try!!

can some one help if what i go wrong ?

Sub test()
Dim LR As Long, i As Long, R As Long
Set objDataRange = Sheet1.UsedRange
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LR
For R = 2 To LR
If Sheet1.Cells("*" &( i, 4)) & "*").Value = Sheet1.Cells(R, 1) Then
Sheet1.Cells(R, 2).Value = Cells(i, 5)
End If
Next R
Next i
MsgBox ("ok")
End Sub


Anyone can help me modify or please suggest

[slamet Harto]

I would prefer to use formula, see attached

[mbarron]

Put the following in a standard module. rCell is, in your sample sheet the A column value and rFromRange is the table in the D and E columns. The formula would be =findIn(A2,$D2$E5):


Function findIn(rCell As Range, rFromRange) As Variant
Application.Volatile
Dim cCell As Range
For Each cCell In rFromRange
If InStr(1, rCell, cCell) > 0 Then
findIn = cCell.Offset(0, 1)
End If
Next
If findIn = "" Then
findIn = "No Match"
End If
End Function