Return multiple values into seperate columns via vlookup?

[AndrewP70]

I currently have a spreadsheet with over 5000 lines of data. Column A contains the duplicate items and column B contains the alternative part numbers I require in separate columns. Vlookup will return the first value, however some items in column A will have 5 alternatives.

Item Alt Alt Alt Alt
70080050TC TC12351
70080050TC PR3134
70080050TC PR4509
70080050TC PR5337
70080050TC PR5646

70080050TC TC12351 PR3134 PR4509 PR5337 PR5646 - This is what I am trying to achieve. Is this possible?

Once again many thanks for your help.

 

[ZVI]

Hi Andrew,

Comments about "formula" solution:
1. Excel's VLOOKUP function can't return multiple values, but its VBA alternative can do that of course.
2. Only array formula which is entered into some cells can return separate values into those cells.
And as it's previously unknown how many values are expected in result, that array formula should be entered into array of cells with some extra/reserved columns.

VBA alternative behavior can be:
1. UDF which is entered as array formula into selected cells in a row via Ctrl-Shift-Enter with some extra selected columns as we don't know how many columns are required for the result.
2. Macro which is called as usual, for example, by Alt-F8 or via button linked to that macro. You have to define in this macro the 1st cell of destination range.

Example of VBA solution:

Function Lookups(Lookup_Value, ByVal Table_Array As Range, Col_Index_Num As Long)
' ZVI:2016-05-31 http://www.mrexcel.com/forum/excel-questions/706471-help-formula-extract-all-8-character-alpha-numeric-ids-excel-column-2.html#post4537576
 
  Dim a(), b(), v
  Dim i As Long, j As Long, k As Long
  Dim s As String
  Dim IsString As Boolean
 
  ' Limit data by used range to allow full columns referense without slowing the code
  Set Table_Array = Intersect(Table_Array, Table_Array.Worksheet.UsedRange)
  IsString = VarType(Lookup_Value) = vbSingle
 
  ' Copy data to arrays to speed up the code
  With Table_Array
    a() = .Columns(1).Value
    b() = .Columns(Col_Index_Num).Value
  End With
 
  ' Main
  With CreateObject("Scripting.Dictionary")
    For i = 1 To UBound(a)
      If IsString Then
        If StrComp(a(i, 1), Lookup_Value, vbTextCompare) = 0 Then .Item(b(i, 1)) = vbNullString
      Else
        If a(i, 1) = Lookup_Value Then .Item(b(i, 1)) = vbNullString
      End If
    Next
    j = .Count
    If j Then
      ' Found - put result in v()
      v = .Keys
      ' Replace #N/A in extra columns of the UDF's destination cells by vbNullString
      With Application
        If TypeName(.Caller) = "Range" Then
          ' UDF is called from cells
          k = .Caller.Columns.Count
          If k > j Then
            ReDim Preserve v(k - 1)
            For i = j To k - 1
              v(i) = vbNullString
            Next
          End If
        End If
      End With
    Else
      ' Not found - return vbNullString instead of #N/A
      v = Array(vbNullString)
    End If
  End With
 
  ' Return
  Lookups = v
 
End Function

How to use it as UDF in cells:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Item	Alt Ref.		Lookup_Value	Cells of array formula
2	68X88X9T	aaa		68X88X9TC	TC12647	PR8359	PR8410	X3885
3	68X88X9TC	TC12647		Select E2:J2, put formula =Lookups(D2,A:B,2) and confirm it by Ctrl-Shift-Enter
4	68X88X9TC	PR8359
5	68X88X9TC	PR8410		68X88X9T	aaa	bbb	ccc
6	68X88X9T	bbb		Select E5:J5, put formula =Lookups(D5,A:B,2) and confirm it by Ctrl-Shift-Enter
7	68X88X9TC	X3885
8	68X88X9T	ccc		ABCD
9				Select E8:J8, put formula =Lookups(D8,A:B,2) and confirm it by Ctrl-Shift-Enter
Sheet1

<br />

And how to call Vlooups function via macro with putting the result into cells:

Sub How_To_Call_Vlookups_in_VBA()
  Dim a
  a = Lookups("68X88X9TC", Range("A:B"), 2)
  Range("E11:J11").ClearContents
  Range("E11").Resize(, UBound(a) + 1).Value = a
End Sub

Regards

 

[ZVI]

By the way, instead of LOOKUP the Vlookups supports negative indexes as well.
Please take into account that if lookup_value not found then Vlookups returns vbNullString instead of #N/A

 

[AndrewP70]

Hi Ziv,
I must be doing something wrong as I cannot get the macro to run.
You have to define in this macro the 1st cell of destination range? What is required here?
Sorry but sometimes this is way beyond me.
Many Thanks

 

[ZVI]

... I cannot get the macro to run.
You have to define in this macro the 1st cell of destination range? What is required here?

Copy example of the data from post #2 to you sheet.
Copy both code of Function Lookups() and Sub How_To_Call_Vlookups_in_VBA() into the same VBA module.
Then press Alt-F8 and run macro How_To_Call_Vlookups_in_VBA
Result will be in E11:G11
In the macro code the A:B is the Table_Array range, E11 is the first cell for result. Other values of result will be on the right side of E11.
Code line Range("E11:J11").ClearContents just erases previous result.