Vlookup Multiple answers and place in label captions

[jayjay3489]

Hey

Ive came up stuck. I have a table of answers on "Input" sheet which have the same value im looking up but i need in my labels to show different answers in each.

for example my table is

78214 09/06/2015 MABEY HIRE LIMITED TRANSPORT/DELIVERY CHARGE INCL
78214 09/06/2015 MABEY HIRE LIMITED EMV-300 KIT MOUNTED VIBRATOR T
78214 09/06/2015 MABEY HIRE LIMITED TRANSPORT/COLLECTION CHARGE
78214 09/06/2015 MABEY HIRE LIMITED PLEASE ADHERE TO SITE DELIVERY
78201 09/06/2015 CHS NETWORKS LIMITED ADSL/SITE SET UP (£TBA)
78199 09/06/2015 ZEN INTERNET LIMITED PHONE LINE INSTALLATION (£TBC
78199 09/06/2015 ZEN INTERNET LIMITED ZEN LITE 20GB
78199 09/06/2015 ZEN INTERNET LIMITED PHONE LINE MONTHLY RETNAL (£T
78174 09/06/2015 DAVRO - T/A DAVLAV DAL3475 24' X 9' CANTEEN UNIT
78174 09/06/2015 DAVRO - T/A DAVLAV DAL3435 20' X 9' DRYING ROOM


and my code is

If Len(tboorder.Text) = 5 Then
On Error Resume Next
x = Application.WorksheetFunction. _
VLookup(Val(tboorder.Text), Worksheets("Input").Range("A3:H200"), 4, False)
If Err.Number = 0 Then
Label3.Caption = x
Label4.Caption = Application.WorksheetFunction.VLookup(Val(tboorder.Text), Worksheets("Input").Range("A3:H200"), 4, False)
Label5.Caption = Application.WorksheetFunction.VLookup(Val(tboorder.Text), Worksheets("Input").Range("A3:H200"), 4, False)
Label6.Caption = Application.WorksheetFunction.VLookup(Val(tboorder.Text), Worksheets("Input").Range("A3:H200"), 4, False)
Label7.Caption = Application.WorksheetFunction.VLookup(Val(tboorder.Text), Worksheets("Input").Range("A3:H200"), 4, False)
Label8.Caption = Application.WorksheetFunction.VLookup(Val(tboorder.Text), Worksheets("Input").Range("A3:H200"), 4, False)
Label9.Caption = Application.WorksheetFunction.VLookup(Val(tboorder.Text), Worksheets("Input").Range("A3:H200"), 4, False)
Label10.Caption = Application.WorksheetFunction.VLookup(Val(tboorder.Text), Worksheets("Input").Range("A3:H200"), 4, False)
Label11.Caption = Application.WorksheetFunction.VLookup(Val(tboorder.Text), Worksheets("Input").Range("A3:H200"), 4, False)
Label12.Caption = Application.WorksheetFunction.VLookup(Val(tboorder.Text), Worksheets("Input").Range("A3:H200"), 4, False)
Label13.Caption = Application.WorksheetFunction.VLookup(Val(tboorder.Text), Worksheets("Input").Range("A3:H200"), 4, False)
Label14.Caption = Application.WorksheetFunction.VLookup(Val(tboorder.Text), Worksheets("Input").Range("A3:H200"), 4, False)
Label15.Caption = Application.WorksheetFunction.VLookup(Val(tboorder.Text), Worksheets("Input").Range("A3:H200"), 4, False)
Label16.Caption = Application.WorksheetFunction.VLookup(Val(tboorder.Text), Worksheets("Input").Range("A3:H200"), 4, False)


Else
Label3.Caption = "No such product found"
On Error Resume Next
End If
On Error GoTo 0
End If

End Sub


Can anyone help. PLEASEEEE

 

[jorismoerings]

Hi,

I don't fully get what you're trying to accomplish.
Are you looking for an equivalent of the VLOOKUP function but one with the posibility to enter the Nth value so you can use it on your worksheet or .....

 

[jayjay3489]

Yeah so as you can see if I place 78214 in a text box I want label captions to show the 3rd column answer. But as you can there is multiple answers to that number so I need label 4 to show 1st result then label 5 to show second answer and so on. Does that make sense

 

[jorismoerings]

Hi,

So it should work as a worksheet function.
Whilst it's possible by using nested match statements, the other option would be to create a UDF.
Copy the following code into a blank module:

Public Function NTHVLOOKUP(Lookup As String, Reference As Range, _ 
    Column As Long, NTH As Long) As Variant 
    Dim arr(), returnValue 
    Dim i As Long, count As Long 
    returnValue = CVErr(xlErrRef) 
    If NTH > 0 Then 
        If Reference.Columns.count >= Column Then 
            arr() = Reference 
            For i = 1 To UBound(arr) 
                If arr(i, 1) = Lookup Then 
                    count = count + 1 
                    If count = NTH Then 
                        returnValue = arr(i, Column) 
                        Exit For 
                    End If 
                End If 
            Next i 
            If count < NTH Then returnValue = CVErr(xlErrNA) 
        End If 
    End If 
    NTHVLOOKUP = returnValue 
End Function

and after that, use it in your spreadsheet as follows:

=NTHVLOOKUP(LookupValue, LookupRange, ReturnColumn, NthValue)

eg

=NTHVLOOKUP("test",A1:D10, 4, 9)

This will return the value of column D for the 9th match of "test"

 

[jayjay3489]

Thank you so much this code works