Excel – How to use multiple VLOOKUP with duplicate data


I have huge excel file. Column A has invoices(duplicate rows since each item in the invoice is a row), column B has SKU value of item bought(like 200ml, 300ml etc), column C has the brand bought(like Coca-Cola,Sprite etc) and column D has the no of items bought(like 10,15 etc).

Table 1

The first table has is the dump file for all invoices and the intems bought

Now i want to find the No. of items bought given the condition that the brand is Coca-Cola, the SKU is 200ml and the invoice no. is XAX1X2X3 and display it in another cell.


Now in the second table, i want to match the invoice with the pack size and brand from first table and put the quantity in the empty cell

So the row that is highlighted in table 2 will show the value 3 cause invoice T1411031400114, pack size 200, brand coca-cola has Qty as 3.

I was thinking of using nested VLOOKUP but cant get the correct formula for it.

Any help will be appreciated.



Best Solution

Consider using sumifs():

eg: =SUMIFS($F:$F,$C:$C,$J3,$D:$D,K$1,$E:$E,K$2)

Check the image below for clarity

Using sumifs to solve the issue

Related Question