PDA

View Full Version : combined Vlookup & Hlookup?

DarrylW
05-27-2010, 01:40 AM
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!

xld
05-27-2010, 02:52 AM
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
05-27-2010, 02:59 AM
Thank you for your response… However all I get is a #VALUE! Error

xld
05-27-2010, 03:03 AM
Did you array-enter them?

DarrylW
05-27-2010, 03:06 AM
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
05-27-2010, 03:12 AM
Two solutions in the attached, formula based and pivot table.

xld
05-27-2010, 03:25 AM
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
05-27-2010, 03:46 AM
Thank you both for your responses. I now have a number of ways to achive what I need to