Selecting a value of a table in Excel

Question

I have a definition table, as well as two variable cells. Based on those I want to select a value from the table. See table below.

EXAMPLE

Variable cell 1 is Fitch, variable cell 2 is CCC+. I want in my output cell, called also Credit quality class, to see the value 6. How can I do 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 statemets for each column, but that is not what I am looking for.

Answer 1

A Double Lookup

• In Microsoft 365 you can simply do:

  =IFERROR(XLOOKUP(G2,XLOOKUP(G1,A1:C1,A2:C6),D2:D6),"")
  

• If you don't have it, you can use INDEX/MATCH:

  =IFERROR(INDEX(D2:D6,MATCH(G2,INDEX(A2:C6,,MATCH(G1,A1:C1,0)),0)),"")
  

• Either way, study the following to better understand how INDEX/MATCH work.

Answer 2

You may use SUMPRODUCT:

=SUMPRODUCT(($A$2:$C$6=H5)*($A$1:$C$1=H4)*D2:D6)

This formula will work as long as variables are correct, and each variable 2 is unique in each column.

Answer 3

If you have Excel 365 you can use this formula:

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