Convert Index Match to VBA to Reference a Matrix

[Domithious]

Hello I'm trying to convert a Excel formula to VBA using Index and Match.

The formula i have is
=IFNA(INDEX('Sheet2'!$I$4:$M$8,MATCH([@[Height]],'Sheet2'!$H$4:$H$8,0),MATCH([@[Weight]],'Sheet2'!$I$3:$M$3,0)),"")

I have a Matrix in 'Sheet2' worksheet. In 'Sheet1' I have a table with Height and Weight columns and I'm trying to find the corresponding Matrix result for a third column.

I tried to record a Marco, but that doesn't really provide what i need as more and more rows will be added to the table.

Any advice would be much appreciated.

 

[severynm]

Hi!

It's not too difficult to port worksheet formulas into VBA because you can use Application.WorksheetFunction to directly call the worksheet functions in code, e.g. Application.WorksheetFunction.Match(...). It also looks like you're dealing with excel tables, so you might refer to this to help you with the syntax for (properly) dealing with tables.