Selecting a value of a table in Excel

Question

I have two variable cells and a definition table. I want to choose a value from the table based on that. View the table below.

EXAMPLE

Fitch and CCC+ are the two variables in cell 1. I want the number 6 to appear in my output cell, also called Credit quality class. How can I go about doing that?

TABLE

Fitch	Moody's	S&P	Credit quality class
AAA	Aaa	AAA	0
AA+	Aa1	AA+	1
BBB+	Baa1	BBB+	3
CCC+	Caa1	CCC+	6
RD	/LD	SD	6

I tried using INDEX and MATCH functions, but I do not obtain the correct result. It does work if I use IF statement for each column, but that is not what I am looking for.

Answer 1

Use this formula:

=LET(step1,FILTER(A1:D6,A1:D1=B11),
INDEX(D1:D6,MATCH(B12,step1,0)))