Vlookup on value given in textbox1 & textbox2 & return value in another testbox3 from sheet2

Question

I need help in excel vba code for vlookup with 2 values provided from textbox 1 & textbox 2.

I have a worksheet in Thisworkbook called lookup (dynamic table)Image Sheet with lookup data

I have an userform with 3 textboxs where user will provide textbox 1 & textbox2 values if found in lookup columnc then textbox3 must return the vlookup value from col c from above sheet Image of Userform

he lookup table rows are dynamic but columns are fixed so I have written the following code to get the range, textbox1 & textbox2 values then passing it to a function to get the value in textbox 3

But the code is not working nor I understand how to pass 2 conditions from textbox to vlookup & return value in 3rd box

Code

Private Sub CommandButton1_Click()

Dim wb As Workbook
Dim sht As Worksheet
Dim userange As Range
Dim lastrow As Long
Dim lastcolumn As Long
Dim startcell As Range

'Finding the dynamic table range in sheet lookup

Set sht = ThisWorkbook.Worksheets("lookup")
Set startcell = Range("A1")

'Find Last Row and Column
    lastrow = sht.Cells(sht.Rows.Count, startcell.Column).End(xlUp).Row
    lastcolumn = sht.Cells(startcell.Row,                sht.Columns.Count).End(xlToLeft).Column

'select range
   userange = sht.Range(startcell, sht.Cells(lastrow, lastcolumn)).Select

'Constraints from 2 textboxs given in userform
Dim cit1 As String
cit1 = TextBox1.Value 'textbox1
Dim cit2 As String
cit2 = TextBox2.Value 'textbox2

'calling vlookup function by passing the lookup range from above, return        value in col D if col B in excel sheet(lookup) has textbox 1.value & col C in excel sheet(lookup) has textbox2.value

Two_Con_Vlookup(userange,D,cit1,cit2)
End Sub

Function Two_Con_Vlookup(Table_Range As Range, Return_Col As Long, Col1_Fnd, Col2_Fnd)

Dim rCheck As Range, bFound As Boolean, lLoop As Long
On Error Resume Next

Set rCheck = Table_Range.Columns(1).Cells(1, 1)

With WorksheetFunction

    For lLoop = 1 To .CountIf(Table_Range.Columns(1), Col1_Fnd)

       Set rCheck = Table_Range.Columns(1).find(Col1_Fnd, rCheck, xlValues, xlWhole, xlNext, xlRows, False)

       If UCase(rCheck(1, 2)) = UCase(Col2_Fnd) Then

            bFound = True

            Exit For

        End If

    Next lLoop

End With

If bFound = True Then

    Two_Con_Vlookup = rCheck(1, Return_Col)
    'textbox3 must return with col D
    TextBox3 = Two_Con_Vlookup.Value

Else

 Two_Con_Vlookup = "#N/A"
  TextBox3 = Two_Con_Vlookup.Value

End If

End Function

Aditya Pansare Aditya Pansare · Accepted Answer · 2016-11-04T04:58:19

This might not be the best solution, but it will give you the result you are seeking for.

Create a helper column in your 'lookup' sheet concatenating column A and column B.

Then lookup with the concatenation of Textbox1 and Textbox2.

Vlookup on value given in textbox1 & textbox2 & return value in another testbox3 from sheet2

1 Answers