Selecting a value of a table in Excel

0 votes

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 by Kithuzzz
• 38,010 points
184 views

1 answer to this question.

0 votes

Use this formula:

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

enter image description here

answered Feb 7, 2023 by narikkadan
• 63,420 points

Related Questions In Others

0 votes
1 answer

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

answered Nov 8, 2022 in Others by narikkadan
• 63,420 points
526 views
0 votes
1 answer

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

answered Dec 25, 2022 in Others by narikkadan
• 63,420 points
434 views
0 votes
1 answer

How can find location of value in a square using excel

For the cell address: =LET(ζ,B4:F11,BASE(MIN(IF(ζ=M ...READ MORE

answered Jan 7, 2023 in Others by narikkadan
• 63,420 points
233 views
0 votes
1 answer

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

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

answered Jan 29, 2023 in Others by narikkadan
• 63,420 points
432 views
0 votes
1 answer

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

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
1,163 views
0 votes
1 answer

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

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

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
1,393 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

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

Feb 17, 2022 in Others by Edureka
• 13,670 points
686 views
0 votes
1 answer

IF - ELSE IF - ELSE Structure in Excel

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

answered Feb 18, 2022 in Others by gaurav
• 23,260 points
2,265 views
0 votes
1 answer

Excel - Make a graph that shows number of occurrences of each value in a column

There is probably a better way to ...READ MORE

answered Oct 21, 2022 in Others by narikkadan
• 63,420 points
7,387 views
0 votes
1 answer

How can I use a command button in excel to set the value of multiple cells in one click?

Try this: Private Scan As Integer Private Sub CommandButton1_Click() ...READ MORE

answered Oct 24, 2022 in Others by narikkadan
• 63,420 points
501 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP