1

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

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.

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)

However, this formula gives me #NV.

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

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.

Michi
  • 4,663
  • 6
  • 33
  • 83
  • I found your formula: =VLOOKUP(E2&"-"&E3,CHOOSE({1,2},A2:A25&"-"&B2:B25,C2:C25),2,FALSE) from the website here https://www.smartsheet.com/advanced-vlookup-multiple-criteria but I still get #NV as result. – Michi Oct 19 '19 at 09:39
  • Did you try =VLOOKUP(E2&E3,CHOOSE({1\2}$A$1:$A$9&$B$1:$B$9,$C$1:$C$9),2,FALSE)? and it is a matricial function so instead of introduce by enter, use, CTRL+SHIFT+ENTER – David García Bodego Oct 19 '19 at 09:51
  • Anyhow, next time, a small research on SO will [help](https://stackoverflow.com/questions/21576595/searching-an-excel-with-two-search-words-multiple-lookup-using-vba-macro/21582535#21582535) you... – David García Bodego Oct 19 '19 at 09:57
  • 1
    Don't use concatenation of multiple columns in a lookup @DavidGarcíaBodego. It's very error prone – JvdV Oct 19 '19 at 10:00

3 Answers3

1

I would strongly recommend using SUMIFS instead of VLOOKUP because this would make sure if you have multiple same values it will add them together:

=SUMIFS(C:C,A:A,E2,B:B,E3)

But this function will only work in newer versions of Excel if you need it for an older version of excel you need to work with SUMPRODUCT

Optional you can also use an alternative VLOOKUP which would be a combination of INDEX and MATCH. There are two options, either the array formula with the "{}" can in some cases result to wrong data, but is easy to understand:

{=INDEX(C:C,MATCH(E2&E3,A:A&B:B,0))}

This is a matricial formula so the "{}" are added by Excel when you type the formula regularly by pressing CTRL + SHIFT + ENTER

otherwise use. I would always recomend using regular formulas over matricial formulas

Quote user JvdV

Formula in E4:

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

Community
  • 1
  • 1
Andreas
  • 23
  • 8
  • 1
    Upvoted for the use of `SUMIFS`, but the second option is not good IMHO. The use of concatenation of values on itself is very error prone, and the use of whole column ranges extremely slow. You might want to consider to scratch that. – JvdV Oct 19 '19 at 10:40
1

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.

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.

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 =)

enter image description here

Formula in E4:

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

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

JvdV
  • 70,606
  • 8
  • 39
  • 70
1

The shortest and fastest formula for 2 criteria Lookup is using DGET()

Table layout as below :

    A         B           C                 D               E               F
1  Products     Brands      Revenue     Search Criterias
2  Product A    Brand1      500           Products         Brands         Revenue
3  Product B    Brand3      800           Product C        Brand3
4  Product B    Brand2      900
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

Formula in F3 :

=DGET(A1:C9,F2,D2:E3)

and, will return the desired result : 300

bosco_yip
  • 3,762
  • 2
  • 5
  • 10