Partially search through a column and return corresponding value in another column

[imran1059]

Hi,

I need to put a formula in Column M which will search through column L and match it (partially or completely) with column O and display the value available in same row in column P. If VBA code will do it faster then provide VBA code please.

e.g. in Column L, it is written as "Salary of Emp#784" so in Column M it should be just "Salaries"

[TABLE="width: 168"]
<colgroup><col span="2"></colgroup><tbody>[TR]
[TD]Column O[/TD]
[TD]Column P[/TD]
[/TR]
[TR]
[TD]Donation
[/TD]
[TD]Donations[/TD]
[/TR]
[TR]
[TD]salary[/TD]
[TD]Salaries[/TD]
[/TR]
[TR]
[TD]Commerce
[/TD]
[TD]Legalization
[/TD]
[/TR]
[TR]
[TD]Housing
[/TD]
[TD]Housing Allow.
[/TD]
[/TR]
[TR]
[TD]Bank[/TD]
[TD]Bank Charges[/TD]
[/TR]
[TR]
[TD]GSM[/TD]
[TD]Communications[/TD]
[/TR]
[TR]
[TD]Stamp[/TD]
[TD]Legalization[/TD]
[/TR]
[TR]
[TD]Trip[/TD]
[TD]Business Trip[/TD]
[/TR]
[TR]
[TD]Chamber[/TD]
[TD]Legalization[/TD]
[/TR]
</tbody>[/TABLE]


Thanks,
Imran.

 

[mumps]

Try:

Sub FindMatch()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim rng As Range, foundRng As Range
    For Each rng In Range("O2:O" & LastRow)
        Set foundRng = Range("L:L").Find(rng, LookIn:=xlValues, lookat:=xlPart)
        If Not foundRng Is Nothing Then
            Range("M" & foundRng.Row) = rng
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

 

[jtakw]

Hi,

I'm a little confused with your description, something like this:

Book1
	L	M	N	O	P
1	Salary of Emp#784	Salaries		Donation	Donations
2	Emp#888 made a donation	Donations		salary	Salaries
3	Company field trip	Business Trip		Commerce	Legalization
4				Housing	Housing Allow.
5				Bank	Bank Charges
6				GSM	Communications
7				Stamp	Legalization
8				Trip	Business Trip
9				Chamber	Legalization
Sheet386

Cell Formulas
Range	Formula
M1	=LOOKUP(2,1/SEARCH(O$1:O$9,L1),P$1:P$9)

Formula copied down.

 

[imran1059]

Thanks a lot. This formula works very well.

 

[imran1059]

Dont know why but this code is not giving me desired output. My problem is solved with the formula provided by jtakw. Anyway, thanks for the help.

 

[jtakw]

Thanks a lot. This formula works very well.

You're welcome, glad it worked for you.