Get and use cell reference from a two-dimensional lookup

Context: manufacturing plant

User input: On sheet Incidents, the user will enter events that disrupt production. The Incidents sheet has headers in Row 1 for various columns. Two of the columns (B & E) capture Production Run Number and Work Station Name.

Macro output: The goal of the macro is to populate a blank grid on a 2nd sheet (let’s call it Summarize) used to summarize the results to support making a Pareto chart. The blank grid is framed, if you will, by the Production Run Numbers in Column B and the Work Station Names in Row 20. So the upper left cell of the blank grid starts in C21.

The need: Code to go through each row on the Incidents sheet, use the Production Run Number and Work Station Name entries in Columns B & E to find the one corresponding cell reference on the Summarize sheet and populate it with information.

What I’ve looked at:
- A two-dimensional lookup returns the value of the found cell, but these cells will initially be blank and I want to populate them.
- I believe using SUMPRODUCT (a variant of a two-dimensional lookup) requires the grid to already be populated with cell values.
- Index Match seems to provide the cell value, whereas I’m thinking I need a cell reference to populate row and column variables to use in a For Next loop as the macro goes through the rows on the Incidents sheet.
- Using a formula such as =ADDRESS(MATCH(‘Incidents'!B7,’Summarize‘!B21:B40,0),MATCH(Incidents'!E7, ’Summarize‘!C20:P20,0)) returns an address such as $A$1, which only means the relative address within the blank array (so “$A$1” is really C21 on the sheet).

What VBA coding will fill the need, please? (I'm using Excel 2010.)


Many thanks in advance!

Example file uploaded, with before and after! Thanks

Thank you very much! It works like a charm.

Now I just have to crawl through it to figure out exactly how it works so I can add to it. Each populated cell on sheet Summarize - Before will get 2 additional attributes based on the entries on sheet Incidents: a fill color depending on the corresponding Cause Type entered in Col D, and a comment containing the corresponding text in Col F.

Appreciate the rapid solution, and my apologies for not responding sooner (I was out of pocket Friday afternoon until now).

Thanks, I was wondering how to show the question was answered!