Return multiple values into seperate columns via vlookup?

AndrewP70

New Member
Joined
Feb 17, 2016
Messages
26
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.
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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:
Rich (BB code):
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
ABCDEFGHIJK
1ItemAlt Ref.Lookup_ValueCells of array formula
268X88X9Taaa68X88X9TCTC12647PR8359PR8410X3885
368X88X9TCTC12647Select E2:J2, put formula =Lookups(D2,A:B,2) and confirm it by Ctrl-Shift-Enter
468X88X9TCPR8359
568X88X9TCPR841068X88X9Taaabbbccc
668X88X9TbbbSelect E5:J5, put formula =Lookups(D5,A:B,2) and confirm it by Ctrl-Shift-Enter
768X88X9TCX3885
868X88X9TcccABCD
9Select 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:
Rich (BB code):
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
 
Upvote 0
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
 
Last edited:
Upvote 0
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
 
Upvote 0
... 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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,958
Messages
6,175,636
Members
452,662
Latest member
Aman1997

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top