Hi All,

I have workbook with 10k row.

I need to match with these cell with another column and get result.

For Eg.,
Sheet 1
Col A Col B
Bristol B Cheddar
Gloucester The Road

sheet2
Col A
Bristol - If Cell value match with sheet1 col A data get me col B
data
in sheet2.

I need to match all possible way

If the value is
"Bristol" - Its match
"Bristol B" - its match
"Brist" - its match
"tol B" - its match

I need all the types matches in vba, Pls guide me to get the output.

Assuming your data starts in row 2, you can use this array** formula:

=INDEX(Sheet1!B2:B10000,MATCH(TRUE,ISNUMBER(SEARCH (A2,Sheet1!A2:A10000)),0))

**Use Ctrl+Shift+Enter to confirm formula, not just Enter.

Note that if you want this to be case-sensitive, replace SEARCH with FIND.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"fi.or.jp.de" wrote:

Hi All,

I have workbook with 10k row.

I need to match with these cell with another column and get result.

For Eg.,
Sheet 1
Col A Col B
Bristol B Cheddar
Gloucester The Road

sheet2
Col A
Bristol - If Cell value match with sheet1 col A data get me col B
data
in sheet2.

I need to match all possible way

If the value is
"Bristol" - Its match
"Bristol B" - its match
"Brist" - its match
"tol B" - its match

I need all the types matches in vba, Pls guide me to get the output.

Something like this should work for you...

Dim R As Range
Set R = Worksheets("Sheet1").Columns("A").Find(Worksheets( "Sheet2"). _
Range("A1").Value, LookAt:=xlPart, MatchCase:=False)
If Not R Is Nothing Then
Worksheets("Sheet2").Range("B1").Value = R.Offset(, 1).Value
End If

Change the A1 and B1 references to match the actual cell addresses you are
using on Sheet2.

--
Rick (MVP - Excel)


"fi.or.jp.de" wrote in message
...
Hi All,

I have workbook with 10k row.

I need to match with these cell with another column and get result.

For Eg.,
Sheet 1
Col A Col B
Bristol B Cheddar
Gloucester The Road

sheet2
Col A
Bristol - If Cell value match with sheet1 col A data get me col B
data
in sheet2.

I need to match all possible way

If the value is
"Bristol" - Its match
"Bristol B" - its match
"Brist" - its match
"tol B" - its match

I need all the types matches in vba, Pls guide me to get the output.