VLOOKUP based on two cells

[Samuurai]

Hi,

I have a spreadsheet that looks like this: (bear in mind, the "|" is the edge of the column

Please Login or Register  to view this content.

Then I have another sheet called Quantities which a similar structure..

The VLOOKUP works, but if I have a duplicate name, like in the above table "MAXI DRESS" - the formulas get confused.

How can I make my VLOOKUP take into account the NAME and the COLOR when it's looking up Quantities?

[NBVC]

If you are looking up quantities..then you can use Sumproduct

e.g

=Sumproduct(--('Quantities'!$A$1:$A$100=$A3),--('Quantities'!$B$1:$B$100=$B3),$C$1:$C$100)

where Quantities!A1:A100 contain the database of Names
B1:B100 the Colors
C1:C100 the quantities

And A3 and B3 are the lookup Name and Colour to find

[Samuurai]

Yay! That worked great! I still don't yet understand how, but I'm figuring it out slowly

Thanks for your help!

[NBVC]

Sumproduct() is a very flexible function...

... check out this article for the uses of Sumproduct and how it was used here.

http://www.xldynamic.com/source/xld.SUMPRODUCT.html