Index and Match Matrix formula to

Question

I'm really stumped with converting Index + Match to VBA. I'm very new to VBA, this is proving to be beyond my abilities.

I have a table in Sheet2 With Columns, 'Case', 'Probability', 'Impact' & 'Severity'. Then a Matrix in Sheet1

My formula (filled down the column) is:

=IFNA(INDEX(Sheet1!$C$2:$G$6,MATCH([@Probability],Sheet1!$B$2:$B$6,0),MATCH([@Impact],Sheet1!$C$1:$G$1,0)),"")

I'm trying to auto-populate 'Severity' in the table based on the values in the Matrix

Table

Matrix

I tried using Application.WorksheetFunction but I don't get any results.

Any advice would be much appreciated.

What is the Worksheet and Table names? Do you want to return the Range of the filtered row? — TinMan
– TinMan, Commented Feb 27, 2022 at 16:42

VBasic2008 · Accepted Answer · 2022-02-27 21:16:57Z

1

VBA Using `INDEX/MATCH` Formula

These will populate the values instead of the formulas.
If you remove the line .Value = .Value, the formulas stay.
Adjust the worksheet and table names.

Option Explicit

Sub TestEdu()
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim ws As Worksheet: Set ws = wb.Worksheets("Sheet2") ' adjust
    Dim tbl As ListObject: Set tbl = ws.ListObjects("Table1") ' adjust
    Dim lcl As ListColumn: Set lcl = tbl.ListColumns("Severity")
    
    With lcl.DataBodyRange
        .Formula = "=IFNA(INDEX(Sheet1!$C$2:$G$6,MATCH([@Probability]," _
            & "Sheet1!$B$2:$B$6,0),MATCH([@Impact],Sheet1!$C$1:$G$1,0)),"""")"
        .Value = .Value
    End With

End Sub

Sub TestCompact()
    With ThisWorkbook.Worksheets("Sheet2").ListObjects("Table1") _
            .ListColumns("Severity").DataBodyRange
        .Formula = "=IFNA(INDEX(Sheet1!$C$2:$G$6,MATCH([@Probability]," _
            & "Sheet1!$B$2:$B$6,0),MATCH([@Impact],Sheet1!$C$1:$G$1,0)),"""")"
        .Value = .Value
    End With
End Sub

answered Feb 27, 2022 at 21:16

VBasic2008

56.9k5 gold badges21 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

DomDomDom Over a year ago

Instead of manually running the Marco is it possible for the code to run if columns 'Probability' 'Impact' are populated? I've tried Set Rng = Range(tbl & "[[Impact]:[Probability]]").Select For Each cell In Rng If cell.Value <> "" Then and Set Rng = Range("tbl[Impact]:tbl[Probability]").Select For Each cell In Rng If cell.Value <> "" Then

VBasic2008 Over a year ago

I guess that's out of scope. Ask another question, copy this code to it and describe what it does and what you want it to do.

Collectives™ on Stack Overflow

Index and Match Matrix formula to

1 Answer 1

VBA Using `INDEX/MATCH` Formula

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

VBA Using INDEX/MATCH Formula

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related

VBA Using `INDEX/MATCH` Formula