Look up values through hierarchies

[rogerfox]

I've searched through all the forum but I cannot get the answer to this question that might be simple, but it's getting a challange for me.

I have the following values on an excel spreasheet:

Location Product Units
NY Printer 130
NY Notebook 123
NY Speaker 99
UK Notebook 32
UK Headset 74
JP Printer 365

Now, what I need to do, is to link the product units of each location with the following sheet and place a "0" if there is nothing on them:

Location Product Units
NY Printer
NY Notebook
NY Speaker
NY Headset
UK Printer
UK Notebook
UK Speaker
UK Headset
JP Printer
JP Notebook
JP Speaker
JP Headset

I tried using the autofilter method on locations of both sheets and then using vlookup to bring the unit values. However, using this method I got selected all rows (including the ones that were hidden) and it went too slow.
I was wondering if I could use arrays or any object (I've already tried using collections and dictionaries but I didn't find them very usefull for this case). Maybe there is another method or function that I am missing? I would really appreciate your help on this

Thanks!

[Aussiebear]

Have you tried Index Match to find the item on your spreadsheet?

[Bob Phillips]

Try using an array formula

=INDEX(Sheet1!C2:C2000,MATCH(1,(A2=Sheet1!A2:A2000)*(B2=Sheet1!B2:B2000),0) )