vba apply index match formula and loop through worklist

I created an index match formula that is inserted as an array. The formula below works as designed when it is inserted manually into the top of the data and copied down. I need to look up a value for each row independently

Please Login or Register  to view this content.

I recorded the code to see how excel would change using an array see below

Please Login or Register  to view this content.

I inserted the formula into a loop to insert down to the last row in the code below. In this version, the code inserts and adjusts for each row but does not appear to create an array and work

Please Login or Register  to view this content.

I adjusted the code to this version and the array appears to create, but the formula does not adjust for each row and every formula still references the first row. as if they were absolute

Please Login or Register  to view this content.

I think I'm close but just dont have it correct I am looking up 2 values in my first worksheet then matching them to 2 values that exist in a common row and then retrieving a different value from the same matching row

I would do it as suggested if I was the one that would do this going forward. This will be done by someone less proficient in excel and will have varying row count each time it runs. I was trying to have vba insert the formula based on row length and simplify it

TMS The code worked perfect Thanks for the help