英文:
Selecting a value of a table in Excel
问题
基于我现在有的定义表格和两个变量单元，我想从表格中选择一个数值。请参见下面的表格。
示例：
变量单元 1 是 Fitch，变量单元 2 是 CCC+。我想在我的输出单元中，也称为信用质量等级，看到数值 6。我该如何做？
表格：
| Fitch | Moody's | S&P | 信用质量等级 |
|---|---|---|---|
| AAA | Aaa | AAA | 0 |
| AA+ | Aa1 | AA+ | 1 |
| BBB+ | Baa1 | BBB+ | 3 |
| CCC+ | Caa1 | CCC+ | 6 |
| RD | /LD | SD | 6 |
我尝试使用 INDEX 和 MATCH 函数，但我没有获得正确的结果。如果我为每列使用 IF 语句，它可以工作，但这不是我要寻找的方法。
英文:
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.
答案1
得分: 2
-
在Microsoft 365中，您可以简单地执行以下操作：
=IFERROR(XLOOKUP(G2,XLOOKUP(G1,A1:C1,A2:C6),D2:D6),"") -
如果您没有这个函数，您可以使用
INDEX/MATCH：=IFERROR(INDEX(D2:D6,MATCH(G2,INDEX(A2:C6,,MATCH(G1,A1:C1,0)),0),"") -
无论哪种方法，都可以学习以下内容以更好地理解
INDEX/MATCH的工作原理。
英文:
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/MATCHwork.
答案2
得分: 1
如果您使用Excel 365，您可以使用以下公式：
=LET(step1,FILTER(A1:D6,A1:D1=B11),
INDEX(D1:D6,MATCH(B12,step1,0)))
英文:
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)))
答案3
得分: 0
=SUMPRODUCT(($A$2:$C$6=H5)*($A$1:$C$1=H4)*D2:D6)
英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论