combined Vlookup & Hlookup?

[DarrylW]

Is it possible to do a combined Vlookup & Hlookup?

I have attached a simple spreadsheet for what I’m trying to achieve

What I would like to do is populate the mini tables on the left, using the data in the table on the right by looking at the Store ID, then look at the price, then pet and how many they have in stock.

Many thanks for any help!

[Bob Phillips]

Try these array formulae

=MIN(IF($H$3:$H$17=$B$1,IF(($I$3:$I$17=$A6)*($H$3:$M$3=B$5),$H$3:$M$17)))

or Store 2

and

=MIN(IF($H$3:$H$17=$B$15,IF(($I$3:$I$17=$A6)*($H$3:$M$3=B$5),$H$3:$M$17)))

for store 3

[DarrylW]

Thank you for your response… However all I get is a #VALUE! Error

[Bob Phillips]

Did you array-enter them?

[DarrylW]

Really sorry, but how would I go about doing that? would that still work if the Store ID were to change on a weekly basis? or More IDs were added?

[p45cal]

Two solutions in the attached, formula based and pivot table.

[Bob Phillips]

Really sorry, but how would I go about doing that? would that still work if the Store ID were to change on a weekly basis? or More IDs were added?

When you type in the formula, use Ctrl-Shift-Enter, not just Enter. Excel will enclose the formula in braces {...}

[DarrylW]

Thank you both for your responses. I now have a number of ways to achive what I need to