VLOOKUP在不同的列中具有多个条件 [英] VLOOKUP with multiple criterias in different columns

问题描述

我有以下Excel电子表格:

I have the following Excel spreadsheet:

        A         B           C                 D               E
1  Products     Brands      Revenue     Search Criterias
2  Product A    Brand1      500           Criteria 1:        Product C
3  Product B    Brand3      800           Criteria 2:        Brand 3
4  Product B    Brand2      900           Revenue:           300
5  Product C    Brand1      200
6  Product C    Brand3      300
7  Product C    Brand4      750
8  Product D    Brand1      450
9  Product C    Brand4      150
10

您可以在 A列中看到产品列表，在 B列中找到相应的品牌，并在 C列中看到收入.

As you can see in Column A I have list of products and in Column B the corresponding brands and in Column C the revenue.

现在，我想使用VLOOKUP根据产品(Cell E2)和品牌(Cell E3)搜索收入.因此，我尝试使用以下公式:

Now I want to use VLOOKUP to search for the revenue based on a product (Cell E2) and a brand (Cell E3). Therefore, I tried to go with this formula:

E4 =VLOOKUP(E2&"-"&E3,A1:C9,3,FALSE)

但是，这个公式给了我#NV.

However, this formula gives me #NV.

我需要更改其公式以使其起作用吗?

What do I need to change in my formula to make it work?

请注意:
我知道我可以通过添加helper column来解决此问题，在该helper column中，我合并 A列和 B列中的数据，然后让VLOOKUP在此helper column.但是，我正在寻找没有此helper column的解决方案.

Please note:
I know I could solve this issue by adding a helper column in which I combine the datas from Column A and Column B and then let the VLOOKUP run over this helper column. However, I am looking for a solution without this helper column.

推荐答案

首先，请注意，您的查找数组没有空格，而查找值却有空格！那将永远不会返回匹配，因此请首先解决该问题.

First of all, please note that your lookup array does not have a space while your lookup value does! That will never return a match, so address that issue first.

此外，我强烈建议不要在永远的多条件查找中连接值！这很容易出错.而是使用布尔逻辑创建1和0的数组，以使用INDEX和MATCH的强大组合.如果您是Excel及其工作表函数的常规用户，请尝试养成在VLOOKUP上使用该组合的习惯.

Furthermore, I would highly recommend to not concatenate values in a multi-criteria lookup, ever! That's very error prone. Instead use boolean logic to create an array of 1's and 0's to use the powerfull combination of INDEX and MATCH. Try to get into the habit of using that combination over VLOOKUP if you are a regular user of Excel and it's worksheet functions.

此处是对此功能组合的简短介绍.就像在那篇文章中所提到的，INDEX会更方便地派上用场，正如您在下面的示例中在创建返回数组时所注意到的那样.这将避免使用必须通过 Ctrl Shift Enter =)

Here is a small introduction to this combination of functions. And as mentioned in that post too, INDEX will come in handy much more often as you will notice in the below example when we create our return array. This will prevent the use of having to enter as an array formula through CtrlShiftEnter =)

E4中的公式:

=INDEX(C2:C9,MATCH(1,INDEX((A2:A9=E2)*(B2:B9=E3),),0))

---

当您在Revenue下实际获得数值时，您可以简单地使用SUMIFS，如其他答案所示.

---

When you actually got numeric values under Revenue you can simply use SUMIFS, as the other answer suggest.

这篇关于VLOOKUP在不同的列中具有多个条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！