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.

Feb 7, 2023 in Others 195 views

Use this formula:

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

• 63,420 points

MS Excel showing the formula in a cell instead of the resulting value

Make sure that... There's an = sign before the formula There's ...READ MORE

Get random value in the range of plus/minus 10% of a cell value in Excel

Why not just use RANDBETWEEN(B2*0.9, B2*1.1) if ...READ MORE

Select a range in Excel based on the value of a cell

Try this: Dim WorkRng As Range Set WorkRng = ...READ MORE

Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

IF - ELSE IF - ELSE Structure in Excel

In this case, you can use nested ...READ MORE