Assuming that your form has a Text Box named "TextBox1" and a Label named "Label1"
the following code can be attached to the TextBox1 change event.
The code responds to each change in the text in the text box.
Whenever there are 6 characters in the text box it runs the Workbook VLOOKUP() function.
Note that the way that the range is referenced is Visual Basic style and not worksheet style.
The result of the VLOOKUP is placed as the label caption. You could apply the result to any other Forms object that accepts text.
You will need to add code to capture errors returned by VLOOKUP() such as when the text string is not found.
If the lookup table on the worksheet consists of numbers in the first column, you will have to convert the text from the text box to a number.
Private Sub TextBox1_Change()
If Len(TextBox1.Text) = 6 Then
Label1.Caption = Application.WorksheetFunction. _
VLookup(TextBox1.Text, Worksheets("Sheet1").Range("D2:E20"), 2, False)
For text to a number try this: VLookup(CLng(TextBox1.Text)...
Note also that if you move the lookup table on the worksheet the code will not change to follow it - the range will have to be manually altered.
Hope this helps