Find a value in single cell (comma separated data) and return the corresponding value

[Nitesh Mathur]

HI,


Im using MS Office 2013 and stuck in a problem.


Problem is: I want to Find a value in single cell (comma separated data) and return the corresponding value, which is in next column.


I have 2 sheets à Sheet1 is Data Table and Sheet2 is Result Table


In Sheet1 I have data in Column A is comma separated, and in column B I have name e.g.


A B

C101,C345,A345,D346,D367,A346 Nicol
S233,A432,E758,D111,D122,D123 Peter



In Sheet2, in Column A cell 2, I put the value A345 then in column B it shows value Nicol and so on.

e.g.


A B

A345 Nicol
D122 Peter
C101 Nicol


Please a macro or excel formula required.


Thanks

NItesh

 

[Debaser]

In B2:

=LOOKUP(2,1/ISNUMBER(SEARCH(","&A2&",",","&Sheet1!$A$2:$A$17&",")),Sheet1!$B$2:$B$17)

and copy down.

 

[Nitesh Mathur]

In B2:

=LOOKUP(2,1/ISNUMBER(SEARCH(","&A2&",",","&Sheet1!$A$2:$A$17&",")),Sheet1!$B$2:$B$17)

and copy down.

HI Debaser
Thanks for your quick reply but what if value is like this FB11,FB13-FB19,FB22,FB37-FB41

The symbol "-" is also used in some of places

Thanks
NItesh

 

[Nitesh Mathur]

In B2:

=LOOKUP(2,1/ISNUMBER(SEARCH(","&A2&",",","&Sheet1!$A$2:$A$17&",")),Sheet1!$B$2:$B$17)

and copy down.

If I want to find out value FB37 then it is shows #NA. Please suggest

Thanks
NItesh

 

[Debaser]

Before I fix that, if your cell contains say FB37-FB40, do you expect to be able to match all of FB37, FB38, FB39 and FB40 to that cell, or only FB37 and FB40?

 

[Nitesh Mathur]

In B2:

=LOOKUP(2,1/ISNUMBER(SEARCH(","&A2&",",","&Sheet1!$A$2:$A$17&",")),Sheet1!$B$2:$B$17)

and copy down.

Thanks a lot Mr. Debaser,

I have modify the formula as per my requirement.

Thanks
NItesh

 

[Nitesh Mathur]

Before I fix that, if your cell contains say FB37-FB40, do you expect to be able to match all of FB37, FB38, FB39 and FB40 to that cell, or only FB37 and FB40?

Yes I would like to match FB37, FB38, FB39, FB40 (individually), if possible.

Thanks
NItesh Mathur

 

[Debaser]

This may help:

Option Explicit
Function LookupValue(ByVal sLookupVal As String, ByVal rgLookupRange As Excel.Range, lColReturn As Long)
    Dim vData
    Dim vMatch
    Dim vParts
    Dim sRanges() As String
    Dim n                     As Long
    Dim x                     As Long
    Dim y                     As Long

    ' default value
    LookupValue = "No match"

    vMatch = Application.Match(sLookupVal, rgLookupRange.Columns(1), 0)

    If Not IsError(vMatch) Then
        LookupValue = rgLookupRange.Cells(vMatch, lColReturn).Value
    Else
        sLookupVal = VBA.UCase$(sLookupVal)
        vData = rgLookupRange.Columns(1).Value
        For n = LBound(vData, 1) To UBound(vData, 1)
            If InStr(1, vData(n, 1), ",") <> 0 Then
                vParts = Filter(Split(vData(n, 1), ","), sLookupVal)
                For x = LBound(vParts) To UBound(vParts)
                    If VBA.UCase$(vParts(x)) = sLookupVal Then
                        LookupValue = rgLookupRange.Cells(n, lColReturn).Value
                        Exit Function
                    End If
                Next x
            End If
            If InStr(1, vData(n, 1), "-") <> 0 Then
                vParts = Filter(Split(vData(n, 1), ","), "-")
                For x = LBound(vParts) To UBound(vParts)
                    sRanges = BuildRange(vParts(x))
                    For y = LBound(sRanges) To UBound(sRanges)
                        If VBA.UCase$(sRanges(y)) = sLookupVal Then
                            LookupValue = rgLookupRange.Cells(n, lColReturn).Value
                            Exit Function
                        End If
                    Next y
                Next x
            End If
        Next n
    End If
End Function
Function BuildRange(ByVal sRange As String) As String()
    Dim vRanges
    Dim lStart                As Long
    Dim lEnd                  As Long
    Dim x                     As Long
    Dim n                     As Long
    Dim sPrefix               As String
    Dim sOut()                As String
   
    vRanges = Split(sRange, "-")
   
    x = 1
    Do While Not IsNumeric(VBA.Mid$(vRanges(0), x, 1))
        sPrefix = sPrefix & VBA.Mid$(vRanges(0), x, 1)
        x = x + 1
    Loop
    lStart = CLng(VBA.Mid$(vRanges(0), x))
    lEnd = CLng(VBA.Mid$(vRanges(1), x))
    ReDim sOut(lEnd - lStart)
    For n = lStart To lEnd
        sOut(n - lStart) = sPrefix & CStr(n)
    Next n
    BuildRange = sOut
End Function

In cell B2:
=LookupValue(A2,Sheet1!$A$2:$B$17,2)
and copy down.