Formula needed for ongoing spreadsheet with a look up

[Zilla]

Hi

I have always used Vlookup but now I need a little bit more.
I have a spreadsheet where 90% of the data and columns will remain the same but the on hand qty will always change.

I created a small sample sheet where I have column A two different stores that care the same product number and on the data sheet they have two different quantities. It appears I might need an IF and a vlookup but not sure how to do this. Can anyone help.

Sample of the sheet below or you can go to drop box and download the file in excel.

https://www.dropbox.com/sh/y05rrxsxf533lor/AAAjfrZkBPzB8QmMM8HgCe1la?dl=0

Spreadsheet

Store Product Product Name Supplier Product qty
Store 111 34 Box S1
Store 222 42 Pencil S2
Store 111 42 Pencil S333
Store 111 67 Paper S4
Store 222 67 Paper S1
Store 222 83 Red Pen S333
Store 111 91 Green pen S22
Store 111 117 Box 2 S2
Store 222 117 Box 2 S1
Store 111 125 Blue Pen S2
Store 111 125 Blue Pen S333
Store 222 141 Paper 1 S4
Store 111 141 Paper 1 S333

Data

Store Product Product qty
Store 111 34 5
Store 222 34 12
Store 111 42 25
Store 111 67 88
Store 111 83 54
Store 222 141 68
Store 111 141 25
Store 222 34 25
Store 222 117 12
Store 111 125 25
Store 222 125 88
Store 111 91 54
Store 222 91 68

 

[Fluff]

How about
=IFERROR(INDEX($K$4:$K$16,MATCH(A4&"|"&B4,INDEX($I$4:$I$16&"|"&$J$4:$J$16,0),0)),"")

 

[Zilla]

How about
=IFERROR(INDEX($K$4:$K$16,MATCH(A4&"|"&B4,INDEX($I$4:$I$16&"|"&$J$4:$J$16,0),0)),"")

How would the formula read if the data was on a separate sheet?
Example instead of I J K it would be A B C.

I tried but it didnt work on a separate sheet.

It did work on the original sheet.

Thank you for that

 

[Fluff]

Can you please supply some data that is accurate to your requirement, rather than a simplified sample.
There are some add-ins here https://www.mrexcel.com/forum/about-board/508133-attachments.html that enable you to include sample data in your post.

 

[Zilla]

How would the above formula be if you had the data on a separate sheet, not the same one?
Please follow link below for sample data with an excel document called "Sample Data with two tabs"

https://www.mrexcel.com/forum/redir...05rrxsxf533lor/AAAjfrZkBPzB8QmMM8HgCe1la?dl=0

 

[Fluff]

How about
=IFERROR(INDEX(Data!$C$2:$C$14,MATCH(A4&"|"&B4,INDEX(Data!$A$2:$A$14&"|"&Data!$B$2:$B$14,0),0)),"")

 

[Zilla]

Yes Thank you

I had an extra ( ) after the & and yours did not and worked. "&(Data!$B$2:$B$14,0)),0)),"")

I appreciate the help

 

[Fluff]

You're welcome & thanks for the feedback