1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel Lookup Range and return range

Discussion in 'Business Applications' started by dprecht, Apr 23, 2010.

Thread Status:
Not open for further replies.
  1. dprecht

    dprecht Thread Starter

    Joined:
    Apr 23, 2010
    Messages:
    1
    I am trying to lookup a range of values within excel and then return a list of values within one cell.

    Here is an example of the data.

    Data Sheet

    Acura A new york
    Acura b charlotte
    acura c los angeles
    acura d San Francisco
    BMW a San Diego
    BMW b Rochester
    BMW c San Antonio


    Lookup Sheet

    Acura a C

    Basically I want to see all the acura values from the range of a to c written to a single cell on the lookup sheet. So after the lookup, the lookup sheet would look like:


    Acura a c New York, Charlotte, Los angeles


    I've tried using a mixture of arrays, matches, lookups, etc... but Im stuck. I think it may require some coding which I am not strong in.

    any help would be appreciated.

    Dave.
     
  2. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Hi there, welcome to the board!

    While someone might get a fancy formula, and I know you could do it that way, I think it'd be too difficult to manage, let alone go back to it in six months and still understand it. I would go for VBA and write a UDF. This does work, assuming the car type is in (specifically in this example I use below) column A, the letters in column B, and city in column C. This also is predecated on the assumption that column B is sorted in ascending order, otherwise it won't work. If that is going to be an issue, we would just need to put in a sort routine in the array. That being said, use this code...

    Code:
    Option Explicit
    
    Const sDelim As String = ", "
    
    Function GETLIST(rLook As Range, vType As Variant, vStart As Variant, vEnd As Variant) As Variant
        Dim wsLook As Worksheet, iStart As Long, iEnd As Long, iStop As Long
        Dim i As Long, arrData() As Variant, vData As Variant
        arrData = rLook.Value
        iStop = 0
        For i = LBound(arrData) To UBound(arrData)
            If LCase(arrData(i, 1)) = LCase(vType) Then
                If arrData(i, 2) = vStart Then iStart = i
                If arrData(i, 2) = vEnd Then iEnd = i
                If iStart > 0 And iStop = 0 Then
                    vData = vData & arrData(i, 3) & sDelim
                End If
                If iEnd > 0 Then iStop = 1
            End If
        Next i
        If iStart = 0 Or iEnd = 0 Then
            GETLIST = "ERROR!"
            Exit Function
        End If
        If Len(vData) > 0 Then
            If Right(vData, Len(sDelim)) = sDelim Then
                vData = Left(vData, Len(vData) - Len(sDelim))
            End If
            GETLIST = vData
        Else
            GETLIST = "ERROR!"
        End If
    End Function
    This goes into a standard module. So follow these steps to install it:
    With the file in question active, hit Alt + F11
    Hit Alt + I, M
    Delete any code and paste the above code (do not duplicate the Option Explicit at the top of the module)

    I had my original data in Sheet2, and the lookup criteria on another sheet (doesn't matter which sheets, but used this for my example), and the formula looked like this...

    Code:
    =GETLIST(Sheet2!$A$1:$C$7,A2,B2,C2)
    There are four parts to the syntax.

    Part 1
    Sheet2!$A$1:$C$7
    This is the range of data you want to look up, column A housed the car make, column B the letter, column C the city

    Part 2
    A2
    This is the cell I stored the criteria for the make I wanted to look up (in my example it housed "Acura")

    Part 3
    B2
    This is the cell I stored the criteria for the letter identifier, to start (in my example it housed "a")

    Part 4
    C2
    This is the cell I stored the criteria for the letter identifier, to end (in my example it housed "c")

    The formula then returned this:
    new york, charlotte, los angeles

    Like I said, if column B is not sorted it will not return all results, as it quits adding to the returned value once the end letter is seen. If neither value is seen it will return the "ERROR!" result.

    HTH
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/918779

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice