Lookup array of values

pootle flump · #1 (**permalink**) 10-21-09, 12:32

Hi

I have the below data in a spreadsheet, columns A-D:

Code:

A       B         C        D
Red     Foo       1        Car
Blue    Foo       2        Truck
Purple  Foo       3        Lorry
Red     Bar       4        Truck
Blue    Bar       5        Moped
Purple  Bar       6        Car

Using VBA (or a worksheet function if required) I would like to pass in the look for the corresponding Value(s) in Column D based on a value found in column A
e.g.
I pass "Red" to the function and I get returned:
"Car" and "Truck".

Essentially I am after the equivalent of:

Code:

SELECT D 
FROM theRange 
WHERE A = "Car"

Many thanks

pootle flump · #2 (**permalink**) 10-21-09, 12:47

I suppose what I want is something like VLOOKUP but that returns an array rather than a scalar.

Colin Legg · #3 (**permalink**) 10-21-09, 13:09

Hi,

The bottom line is that Excel's built-in worksheet functions don't do this well. It's possible to do but it's not pretty (I'll happily demonstrate a native formula solution if you are interested), so a VBA function is a very reasonable choice.

Nate Oliver is a MS Excel MVP and he put this one together, so I would expect it to be pretty solid:
http://www.mrexcel.com/forum/showpos...3&postcount=13

Code:

Public Function foo2( _
    ByRef rngIn As Range, ByRef nameIn As String) As String
Dim tmpArr() As Variant, newArr() As String
Dim i As Long, j As Long
Let tmpArr = rngIn.Value
ReDim newArr(1 To UBound(tmpArr, 1))
For i = LBound(tmpArr, 1) To UBound(tmpArr, 1)
    If tmpArr(i, 1) = nameIn Then
        Let j = j + 1
        Let newArr(j) = tmpArr(i, 2)
    End If
Next
ReDim Preserve newArr(1 To j)
Let foo2 = Join$(newArr, ", ")
End Function

pootle flump · #4 (**permalink**) 10-21-09, 16:45

Thanks again Colin

Yeah that looks pretty logical way to do things. I'll try it tomorrow.

BTW - dunno if you would know this but - I would typically redim preserve the array on each pass of the loop, so the array would never be larger than needed at that moment in time. Looking at this code, that constant resizing might be less efficient than what this guy does (initialises the array to the maximum possible size and then "shrinks-to-fit" at the end). Do you know best practice on this?

Colin Legg · #5 (**permalink**) 10-22-09, 05:11

Hi pootle flump,

I think that's deliberate on his part. As you said, ReDim Preserve is quite an expensive operation so, since you could be looping through thousands of records, it makes sense to only do it at the start and end.

Depending on the big picture (such as user interaction) and if you intend to return the results to a worksheet, another option might be to automate the AdvancedFilter to give you the results. Crude example attached.

pootle flump · #6 (**permalink**) 10-22-09, 05:29

Thanks Colin

I had seen some stuff about advanced filter but rejected it. The array is only used in code for some calculations

Internet Services

Web Development

Digital Marketing

Consumer Tech