VBA to populate cells based on another cell using Index and Match

Hi Experts
Can someone please help me with the below problem

I have a worksheet with some headers and when data is entered in G7, the Index & Match looks at the data in the Database table and auto-populates H7, I7, J7 & K7

So if something is entered in G8, it populates the other cells and so on

Thank You, I have attached a sample file

Regards
Rahul

Not using Index and Match, my preference is auto filer I've set up a solution. To test run "Add Tag"

I did on change to your original file. I converted the table range on the "Database" sheet to a "normal" range. If you need it in table format macro can be modified to
convert this range back to a table again.

Alf

Hi !

As he already had a solution in his previous thread, so a duplicate thread here …

Hi Alf
Thanks for that
The Database sheet needs to be in a table format

Regards
Rahul

Hi Marc
Sorry for the confusion
In the previous thread the request was to do a Index and Match on a table

For some reasons i had to convert the table to a range and because I did that the VBA you had provided stopped working and i cant get it to work on a range
I started a new thread as the requirements changed

Now I need something that would look at the range and do a Index and Match based on what is entered in the column G

The below is the VBA you had given me for a table

Private Sub Worksheet_Change(ByVal Target As Range)
Const D = "Database"
Dim Rg As Range
If Not Intersect(Me.ListObjects("TAG_Data").DataBodyRange.Columns(6), Target(1)) Is Nothing Then
Set Rg = Worksheets(D).ListObjects(D).DataBodyRange.Columns(1).Find(Target(1).Value, , xlValues, xlWhole)
Application.EnableEvents = False
With Target(1, 2).Resize(, 3)
If Rg Is Nothing Then
.ClearContents
Else
.Value = Rg(1, 2).Resize(, 3).Value
Set Rg = Nothing
End If
End With
Application.EnableEvents = True
End If
End Sub


Regards
Rahul

The Database sheet needs to be in a table format

For what it's worth, this line will convert the table "Database" to a normal range

Please Login or Register  to view this content.

and these lines will convert the range back to a table

Please Login or Register  to view this content.

As these commands uses "ActiveSheet" sheet "Datsbase" must be activated, or you can use

Please Login or Register  to view this content.

in which case you don't need to activate the sheet before running the command.

Alf

Thanks, Alf
Suit my needs

Thanks for your help
Regards
Rahul

You are welcome

Thanks for rep and feedback.

Alf