Picking out common "highlighted" cell in table

[Heva]

Hi all, I'm new here so I'm not sure if I am posting in the correct section, and please pardon my poor English

I am working on a project which is putting a table of figures into an Excel table (spanning from C7 to AC20). By using a combination of "Data Validation" and simple "Conditional Formatting", I am able to work out how to highlight a certain row and column of the table by choosing their figures.

However, I am facing a challange of picking out the common "intersected" cell by the highlighted row and column, and putting the figure into another cell (eg. IJ4).
Hence I am seeking help here hoping to find out a Logic formula which manages that function.

Below are some pictures hoping can explain more clearly what I'm trying to get.

\1

L11 is the cell that is intersected by the highlighted row and column

\1

Hope to be able to have IJ4 to automatically pick out the value and have it displayed out like the picture below

\1

Hoping I will be able to find helping hands and guidance along here.
Thanks alot in advance guys!

Cheers

[Pete_UK]

Pictures are not much use, as you can't try things out with them. However, I think this will give you what you want:

=INDEX(D8:AC20,MATCH(E3,C8:C20,0),MATCH(G3,D7:AC7,0))

Hope this helps.

Pete

[Heva]

Pictures are not much use, as you can't try things out with them. However, I think this will give you what you want:

=INDEX(D8:AC20,MATCH(E3,C8:C20,0),MATCH(G3,D7:AC7,0))

Hope this helps.

Pete

Thanks Pete!
Just wondering, is there any way to link this formula to of another sheet in the same file as well?
Say if the table is being moved to Sheet 2 instead but the H, W, L stays in Sheet 1 still.

[Pete_UK]

Yes, you would put the sheet reference and ! in front of each of the ranges, but the E3 and G3 would remain the same, like this:

=INDEX('Sheet2'!D8:AC20,MATCH(E3,'Sheet2'!C8:C20,0),MATCH(G3,'Sheet2'!D7:AC7,0))

Apostrophes are needed around the sheet name if it contains spaces, but there is no harm in putting them in anyway.

Hope this helps.

Pete