Lookup formula with 2 variable

[rizmomin]

Hello:

Please refer to attached file.

I have data as shown in Sheet1
Column B : VendorCode
Column C : Size
Range("F6:N6") is Store Name
Each item has 2 lines of data, 1st line is Inventory and 2nd line is Qty Sold.
Example:
Range("F7:N7") is Inventory for Vendor Code in cell B7 for stores listed in Range("F6:N6")
Range("F8:N8") is Inventory for Vendor Code in cell B7 for stores listed in Range("F6:N6")
and so on

I need formula in sheet2 in cells F10:H down to get data by using vendor code in column C and Store in COlumn D.


Let me know if you have any questions.
Thanks.

Riz

[Pete_UK]

Hello again, Riz.

You can use the following array* formulae in the cells stated in Sheet2:

F10: =INDEX(Sheet1!$C$7:$C$26,MATCH(1,(Sheet1!$A$7:$A$26=$B10)*(Sheet1!$B$7:$B$26=$C10),0))&""

G10: =INDEX(Sheet1!$F$7:$N$26,MATCH(1,(Sheet1!$A$7:$A$26=$B10)*(Sheet1!$B$7:$B$26=$C10),0),MATCH($D10,Sheet1!$F$6:$N$6,0))

H10: =INDEX(Sheet1!$F$7:$N$26,MATCH(1,(Sheet1!$A$7:$A$26=$B10)*(Sheet1!$B$7:$B$26=$C10),0)+1,MATCH($D10,Sheet1!$F$6:$N$6,0))

*Note that array formulae need to be confirmed using the key combination of Ctrl-Shift-Enter (CSE), rather than the usual Enter.

Then the formulae can be copied down to the bottom of your table, as can be seen in the attached file.

Hope this helps.

Pete

EDIT: Sorry, I've just read your post again and realised I have them the wrong way round. The easy way around this is to just change the titles, but if you want to keep them in order then you will need to insert the +1 shown in red above into the formula in G10, and remove it in the formula in H10. Remember to use CSE each time to confirm the array formula, then copy down.

Pete

[rizmomin]

Hello Pete:

Great Work, Thanks a lot

R

[Pete_UK]

You're welcome, Riz - thanks for the rep.

Pete