Reputation: 163
A friend ask me to make something like database but in Excel file. I have an issue with function. I use
=IF(AND((B2=(VLOOKUP(B2;Catalogue!A:F;1;FALSE)));(C2=(VLOOKUP(B2;Catalogue!A:F;2;FALSE)));(D2=(VLOOKUP(B2;Catalogue!A:F;3;FALSE))));VLOOKUP(B2;Catalogue!A:F;4;FALSE);"not found")
If i type Corner/ff/x15 it shows right results
But if i type Filter/k/r it returns not found. As far as i can see, checks only the first Filter. I mean that it checks only the Product and i finds second name of product it doens't match the other cells. Sorry for my English. Can anyone help me please?
Upvotes: 1
Views: 59
Reputation: 71598
I'm assuming that the product, category and subcategory fields in combination are unique, otherwise even if your current formula worked, it would only return the first matched result.
In that case, you should be able to use SUMIFS
since the value you are returning are numbers and the combination of fields are unique:
Price1, Price2 and Price2*24% in order:
=SUMIFS(Catalogue!D:D; Catalogue!A:A; B2; Catalogue!B:B; C2; Catalogue!C:C; D2)
=SUMIFS(Catalogue!E:E; Catalogue!A:A; B2; Catalogue!B:B; C2; Catalogue!C:C; D2)
=SUMIFS(Catalogue!F:F; Catalogue!A:A; B2; Catalogue!B:B; C2; Catalogue!C:C; D2)
You can also use the following for Price1 and drag right thanks to the locked references:
=SUMIFS(Catalogue!D:D; Catalogue!$A:$A; $B2; Catalogue!$B:$B; $C2; Catalogue!$C:$C; $D2)
Upvotes: 2
Reputation: 76000
I'd recommend to useINDEX
+ MATCH
. It's way more versatile (and faster). For example:
Formula in E8
:
=INDEX($D$2:$F$4,MATCH(1,INDEX(($A$2:$A$4=$B8)*($B$2:$B$4=$C8)*($C$2:$C$4=$D8),),0),COLUMN(A1))
Drag down and right.
Upvotes: 1