Index and Match Error

Hi All,
At Site A, I have the Qty for each product and store.
At SiteB tab, I have SiteB qty as well. But at Column D on site B tab, I want to display Qty information from SiteA as well.
i.e I want to know the qty from SiteA at Column D, based on the Combination of Prod and Store. (Using Index and Match Method)

Any help is appreciated!

Regards,
Nironto

Thanks SixSense! It is close but I need the combo of prod and store because there are some (Products and store combo) in SiteB that does not appear in siteA.

Regards,
nironto

Appreciate again Sixsense. There is a reason for me to use Index and Match. I am using lookup currently. It works ok, but there will be a serious performance issue when it comes to massive no of records.
Hence, I am changing all the links to using index and match instead. ^_^

Regards,
nironto

Try this formula in cell D2 at sheet SiteB:

=INDEX(SiteA!$C$2:$C$29;MATCH([@Prod]&[@Store];SiteA!$A$2:$A$29&SiteA!$B$2:$B$29;0))

Enter it as array (Ctrl Shift Enter) and copy down as needed.

Of course, when you add more data to sheet SiteA, change references from $29 to appropriate number of rows.

P.S. - if there is no such a product in such a store at sheet SiteA (for example, in sheet SiteA, there is no product ABC151 in store A2), formula returns N/A error; if you want to make it visually nicer, then

=IFERROR(INDEX(SiteA!$C$2:$C$29;MATCH([@Prod]&[@Store];SiteA!$A$2:$A$29&SiteA!$B$2:$B$29;0));"No quantity at site A!")

or whatever other text you want.

Please note that my Excel uses semicolon ; as arguments separator; if formula above does not work, please change semicolon with comma ( , instead of ; ):

=IFERROR(INDEX(SiteA!$C$2:$C$29,MATCH([@Prod]&[@Store],SiteA!$A$2:$A$29&SiteA!$B$2:$B$29,0)),"No quantity at site A!")

Thanks Forment!
Your result is now very close.
My problem is that I cannot use [Prod]&[Store].
It said the name you entered is not valid. Reasons can include :
-the name does not begin with a letter or underscore.
-The name contains a space or other invalid chars

I managed to get for the first row correct only if I use this. And then drag down
=IFERROR(INDEX(SiteA!$C$2:$C$29,MATCH($A$2:$A$2&$B$2:$B$2, SiteA!$A$2:$A$29&SiteA!$B$2:$B$29,0)),"No quantity at site A!")

Sorry guys. This looks like a tough nut to crack.

Regards,
Nironto

Thanks Sixsense and froment!