Excel 索引匹配多个条件 [英] Excel Index Match Multiple Criteria

问题描述

我必须写一个索引 &匹配excel公式，包含多个条件！

I have to write an Index & Match excel formula, containing multiple criteria!

我有 2 张桌子:

我的公式应该具有以下结构:如果梅赛德斯存在于第一列并且梅赛德斯存在于第五列并且如果第二列中的代码与第六列中的代码匹配然后从第七列中取出成本并将其放入第四个空列中:

My formula should have the following structure: if Mercedes exists in the first column and Mercedes exists in the fifth column and if the code in the second column matches the code in the sixth column then take the cost from the seventh column and put it in the fourth empty column:

如果两个条件都不满足，则插入 0.

If both conditions not met, then insert 0.

当我使用 Index &匹配 1 个条件，如上表所示:

It worked when I used Index & Match with 1 criterion, as seen in the above table:

=(INDEX($G$2:$G$4,MATCH(E2,$A$2:$A$6,0),0))

G 是成本列，E2 是第二个品牌列中的价值，A 当然是第一个品牌列.要添加我尝试过的多个条件:

G is cost column, E2 is value in the second brand column and A is, of course, the first brand column. To add multiple criteria I have tried:

=INDEX(G2:G6,MATCH(1,(A2=A2:A4)*(E2=E2:E4),0))

它不起作用！(返回不适用)

It does not work! (returns N/A)

你能告诉我哪里错了吗?

Could you please tell me where I am wrong?

谢谢！

推荐答案

尝试使用组合公式进行匹配:(假设 E 和 F 是匹配项，A 和 B 是要匹配的数组，C 提供输出)

Try matching with a combined formula: (assumes E and F are match terms and A and B are the array to match within, with C providing output)

{=Index(C:C,Match(E1&F1,A:A&B:B,0))}

使用 Ctrl+Shift+Enter 来使用数组

Use Ctrl+Shift+Enter to utilize the array

这篇关于Excel 索引匹配多个条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！