Return Multiple Values based on Lookup Value

[jaydee]

Hi all,

I was wondering if someone could help me to return multiple numbers based on a lookup value. I have a unique SKU (column F), but there are multiple purchase order (PO) numbers associated with it (column J).

What I am trying to get is, based on the SKU number, it would give me all the PO’s related to it in one cell (I.e. see cells F17:G18). The values can be returned in one cell together, or over multiple columns. Is this possible? Right now I put the data into a pivot table, one column has the SKU, and another one has multiple POs, but then I have to transpose the data manually.

Thank you in advance.

[Paul_Hossler]

I'm fond of using User Defined Functions for things like this, but some of the worksheet formula experts might have a non-VBA way

Option Explicit
'Usage: =POandSKU(F11,$F:$F,$J:$J)
Function POandSKU(SKU As String, RangeSKU As Range, RangePO As Range) As String
    Dim s As String
    Dim r As Range
    
    For Each r In Intersect(RangeSKU.Columns(1), RangeSKU.Parent.UsedRange).Cells
        If r.Value = SKU Then
            s = s & Intersect(r.EntireRow, RangePO.Columns(1)).Value & ", "
        End If
    Next
    
    If Len(s) > 0 Then s = Left(s, Len(s) - 2)

    POandSKU = s
End Function

Paul

[jaydee]

Thank you, Paul!
You are an evil genius ^_^

This works good, it saves me hours of work of doing it manually.

Thank you again!!

[Paul_Hossler]

Not a problem

It could always use more error checking (what doesn't)

Paul