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.