Make an index and match function dynamic

Hi,

I have attached a workbook.

I have a calculated value in H2. This value will correspond to values in the table provided. This value is dynamic and will change based on criteria that will be added.

The value H2 is a combination of to "letters" D1 and A2 or A4 and B1 i.e. trace back where the intersection of H2 occurs within the table

In Cells A8 and A9 I am trying to represent which combination of letters will result in an intersection of the value H2. In the example provided it is C and A.

What i would like to do is have Cells A8 and A9 be more dynamic. So if for example the value in H2 changed to say R12,06 then the intersection would be B and B. Therefore Cell A8 = B and Cell A9 = B

The formula is cell A8 and A9 doesn't cater for this as the range is fixed. I need a dynamic range for the index and match function based on the where the intersection of H2 happens to be in the table.

Thanks!

column K

Please Login or Register  to view this content.

See the green cells in the attached file.

Please see attached file with array formula in B8-B9.

Is this something you can work with?

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

See the attached file.

See column M.

Please reply.