PDA

View Full Version : [SOLVED] Return Multiple Values based on Lookup Value



jaydee
01-23-2014, 05:15 PM
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
01-23-2014, 07:19 PM
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
01-23-2014, 07:30 PM
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
01-25-2014, 10:05 AM
Not a problem

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

Paul