VBA Userform - Index Match then Concatenate

[Yodelayheewho]

Hello,
I have 6 pages on my MultiPage object. On page 2, I have a textbox (txtLineCom2) that I need to populate with a combination of concatenated textboxes, plus add additional text using index match to a small table on another worksheet.
1. The code starting at 'Populate Lines > Comments is working, EXCEPT, it is pulling the results from data that's one row above the target row.
2. Once I get this fixed, I need to pull in the Project Manager's contact information via Index Match and include it in the concatenated string on the last line of the code.

Here is an example of how I want the txtLineCom2 populated...
Net 30 days (cboPOTerms)
Best Way, FOB-SP, PP&A (cboShipVia & cboShipType & cboShipChrgs)
Contact Bob Frapples when the item ships (txtShipInstr)
Soupy Sales is your Project Manager (txtPM)
Soupy is responsible for all aspects of your order; including shipping, installation, startup, and invoicing. (fn)
You may contact Soupy at, [office], or [mobile] (This information is on the worksheet: LISTS (S1:X11)

Here is the code in question. Here is a link to the file: https://www.dropbox.com/scl/fi/qrpyoq6c787d2tb5yrznc/TEST4b_Systems-Order-Entry-Master.xlsm?dl=0&rlkey=m9pwdyf2myoll0pt8zu5qpqbd
'*********************************************
'***Formats Uppercase and autofills multipages***
>>> use code - tags <<<

Private Sub txtShopOrdNum_Change()
    txtShopOrdNum.Value = StrConv(txtShopOrdNum.Value, vbUpperCase)
    txtShopOrdNum1.Text = txtShopOrdNum
    txtShopOrdNum2.Text = txtShopOrdNum
    txtShopOrdNum2a.Text = txtShopOrdNum
    txtShopOrdNum2b.Text = txtShopOrdNum
    txtShopOrdNum2c.Text = txtShopOrdNum
    txtPart2.Text = txtShopOrdNum
    txtPart2_1.Text = txtShopOrdNum
'***Fill Job Textboxes on Order Entry MultiPage***
txtCreateJob2 = "JOB" & Left(txtShopOrdNum, 1) & "-" & Right(txtShopOrdNum, 5)
txtGetDetails2 = "JOB" & Left(txtShopOrdNum, 1)
txtJobNoHere2 = "JOB" & Left(txtShopOrdNum, 1) & "-" & Right(txtShopOrdNum, 5)
'Populate Lines > Comments
Dim pos As Integer
Dim fn As String
pos = InStr(cboPM, " ")
fn = Left(cboPM, pos)
txtLineCom2.Text = cboPOTerms _
& vbCrLf & cboShipVia & ", " & cboShipType & ", " & cboShipChrgs _
& vbCrLf & txtShipInstr _
& vbCrLf & cboPM & " " & "is your Project Manager" _
& vbCrLf & fn & " " & "is responsible for managing all aspects of your order; including shipping, installation, startup and invoicing." _
& vbCrLf & "You may contact " & fn & " at"
'***STOPPED HERE***

Thank you in advance! I greatly appreciate the help I receive from this forum!

 

[Marc L]

Hi, on a small table you can try VLOOKUP …​

​