INDEX formula shading user error

[KateJ]

Hi Excel Gurus

I am having issues with my index formula for a risk register. I am certain this is a user error, but I cannot see it for looking.

There are two parts to my problem

In my spreadsheet, columns H and I have a list of 5 data validation/variables each and I wish them to populate row J with the risk rating from the table from rows 40 to 45 dependent on what has been selected from the pull down options in rows H and I. I have changed all the outcome risk ratings to reflect the alphabet so that I could try and solve my issue but I keep going in circles. As I am getting an answer, just not the correct one.

Secondly, is there a way to get column J to also pick up the colour (shading) along with the risk rating? I have previously used conditional formatting but the risk rating table that I have been asked to use has both H & M in yellow, M & L in Green and H & E in red. Is this possible? I have placed the actual risk rating table in the document as well.

Any assistance would be greatly appreciated.

Kind regards

Kate

[menem]

Please try.

From J4
=INDEX(G41:K45,MATCH(H4,G40:K40,0),MATCH(I4,F41:F45,0))

To
=INDEX($G$41:$K$45,MATCH(I4,$F$41:$F$45,0),MATCH(H4,$G$40:$K$40,0))


Edit.
I've got confuse , which table you want to use ?
Row 40-45 or 51-56
because it will effect the solution for condition format.

for 40-45 , you may create a list of table for each shading and use countif to choose color.
but for 51-56 you need to re-verify the index value to choose color.


Regards.

[TMS]

Try:

Please Login or Register  to view this content.

Code the matrix cells with g, y and with the font formatted the same as the fill colour. Then use CF formulae to colour the cells. For example =J4="r" formatted as red.

[KateJ]

Hi

Thank you for your answer. I had my formula in the incorrect order. I am still working on my shading issue.

Kind regards

Kate

[KateJ]

Hi TMS

Thank you for your assistance with my issues. It appears that I put my formula in the incorrect order. I am still uncertain how to get the correct colour from the information. I do not wish to shade J4 a particular colour. I want it to pull the colour from the matrix table below depending on what answer comes up from the H & I column variables. Does this make sense? The actual contents of the data was the L M H E information. I replaced the actual table with the alphabet to try to work out what I was doing incorrectly. The issues still remains that there is both L or M in green and M or H in yellow and H or E in Red. I am not sure how to resolve this.

Kind regards

Kate