CODEWITHSUNDEEP
excelindexingexcel-formulamatchvlookup
Debbie Mp
Debbie Mp

Reputation: 163

database in excelwhat is wrong with vlookup

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

My database is enter image description here

If i type Corner/ff/x15 it shows right results

![enter image description here

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?

![enter image description here

Upvotes: 1

Views: 59

Answers (2)

Jerry
Jerry

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

JvdV
JvdV

Reputation: 76000

I'd recommend to useINDEX + MATCH. It's way more versatile (and faster). For example:

enter image description here

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

Related Questions