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
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
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))
Originally Posted by mbarron
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))
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:
Then in the B column use:HTML Code:=MATCH(VLOOKUP("*" & D2 & "*",$A$2:$A$5,1,0),$A$2:$A$5,0)
HTML Code:=INDEX($E$2:$E$5,VLOOKUP(ROW()-1,$F$2:$F$5,1,0))
Originally Posted by mbarron
Thanks very much, but it's was shown #N/A when frequency record!
can it be solve ?
Can it applied to VBA for if col D match part of col A then col B value = col E?
Originally Posted by 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
Originally Posted by KK1966
Anyone can help me modify or please suggest
I would prefer to use formula, see attached
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