How to find row number and include in formula

Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file and a mocked up solution.

So, you're looking for the contents of cell $L$3 in the current row of column A. So, on row 3, you are looking for $L$3 in cell A3.

Where do you insert the row? And what do you want to happen? A28 should stay the same?

If you click the button at the bottom of sheet one, use the form to add a new record using data that does not match any of the dummy data. After the record is inserted, check the formula in column H.
The row reference for that record and the one below it should not match the row the data is in.

Cell L3 in Sheet 2 contains the record text of what was selected in the combo box on Sheet 1. Then, the formula being references in column gets that value and finds all matches of that selection in column A in the database.

Hope that makes sense. It also happens if you insert a row directly in the database by inserting a new row.

After inserting a new record using a UserForm, the record is inserted OK but the row number is now off by one on all the following records.

I don't think I was able to reproduce the problem.

That said, if it DOES happen, I suggest you re-establish the formulae in columns I:K. Do that by calling the subroutine below.

Option Explicit

Sub sReestablishFormulaeOnMasterData()

' Call this subroutine AFTER a record has been added to the Table

' Original formulae examples
'=--ISNUMBER(IFERROR(SEARCH($L$3,A3,1),""))
'=IF(H3=1,COUNTIF($H$3:H3,1),"")
'=IFERROR(INDEX($A$3:$A$127,MATCH(ROWS($I$3:I3),$I$3:$I$129,0)),"")
'=FILTER(A3:A50,ISNUMBER(SEARCH(L3,A3:A50)))

Dim lLR As Long     ' last row/record

Application.ScreenUpdating = False
With Sheets("MasterData")
    ' determine the last row of the data
    lLR = .Range("A" & .Rows.Count).End(xlUp).Row
    ' Insert the formulae starting with the
    ' Dynamic Array formula in cell K3
    .Range("K3").Formula2 = _
        "=FILTER(TableX[Web Site],ISNUMBER(SEARCH($L$3,TableX[Web Site])))"
    
    .Range("J3:J" & lLR).Formula = _
        "=IFERROR(INDEX(TableX[Web Site],MATCH(ROWS($I$3:I3),$I$3:$I$" & lLR & ",0)),"""")"
    .Range("I3:I" & lLR).Formula = _
        "=IF($H3=1,COUNTIF($H$3:$H3,1),"""")"
    .Range("H3:H" & lLR).Formula = _
        "=--ISNUMBER(IFERROR(SEARCH($L$3,A3,1),""""))"

    ' Resize the Table to exclude column H
    ' This would have extended the Table
    ' when the formula was added to the column (H)
    ActiveSheet.ListObjects("TableX").Resize Range("$A$2:$G$" & lLR)
    ' Note that this also resizes the rows accurately
    
    ' Tidy up the formatting
    With .Range("H2").Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End With
Application.ScreenUpdating = True

End Sub

Please note that this has been tested independently. It has NOT been tested when called from elsewhere. As stated, it should be called AFTER a row has been inserted to maintain the integrity of the formulae. Unfortunately, because the application changes the Excel environment, I am not prepared to test it IRL. If the (original) code fails/crashes for any reason, it could leave a lot of sorting out to do ... and I'm not going to take that risk.

You're welcome. Thanks for the rep.