How to use VBA for vlookup to return multiple values (code inside)

internct

New Member
Joined
Jun 16, 2015
Messages
3
So my excel have tens of thousands of rows and some of them contain the same information. For example, there are part numbers that we get from multiple suppliers so I would like to use a code to find the multiple suppliers(if a part number has one).
Here is my code ( actually I got this online and customized it to my need). So far, it only returns one value(typically the first value).

By the way, I'm a beginner in vba so I'm still learning all the ternms

Sub FETCH()
On Error GoTo MyErrorHandler:
Dim PN_id As Long
PN_id = InputBox("Enter the Part Number:")
Det = "Part Number : " & Application.WorksheetFunction.VLookup(PN_id, Sheet4.Range("A2:T40018"), 1, False)
Det = Det & vbNewLine & "Part Description: " & Application.WorksheetFunction.VLookup(PN_id, Sheet4.Range("A2:T40018"), 3, False)
Det = Det & vbNewLine & "Supplier: " & Application.WorksheetFunction.VLookup(PN_id, Sheet4.Range("A2:T40018"), 11, False)
Det = Det & vbNewLine & "Car Model: " & Application.WorksheetFunction.VLookup(PN_id, Sheet4.Range("A2:T40018"), 19, False)
MsgBox "Part Details : " & vbNewLine & Det
Exit Sub
MyErrorHandler:
If Err.Number = 1004 Then
MsgBox "Part Number Not Present in the table."
ElseIf Err.Number = 13 Then
MsgBox "You have entered an invalid value."
End If
End Sub


Thanks!
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Vlookup returns the first match. Use VBA Help to learn about The Range.Find Method. This will allow you to find multiple instances of the lookup value.
 
Upvote 0
I'm not familiar with that.
So I found a vlookup that works:
=INDEX($K$2:$K$40874, SMALL(IF($A$40994=$A$2:$A$40874, ROW($A$2:$A$40874)-MIN(ROW($A$2:$A$40874))+1, ""), ROW(A1))) but I would still like the information to be displayed in an input box (similar to my code earlier).
How do I combined this too together?
Thanks!
 
Upvote 0

Forum statistics

Threads
1,213,537
Messages
6,114,216
Members
448,554
Latest member
Gleisner2

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