dynamic/iterative searching

[aaa]

Hi, I hope you can help.
Given multiple inputs I'm trying to automate finding a price from a large
table. The table has following layout (this could be changed if it helps):

Item1 Item1 Item1 Item1
Material1 Material1 Material2 Material2
Style1 Style2 Style1 Style2
Size1 Price1 Price2 Price3 Price4
Size2 Price5 Price6 Price7 Price8
Size3 Price9 Price10 Price11 Price12

The inputs given would be item, material, style and size. The problem with
the LOOKUP functions and INDEX is specifying the column. Specifying the
column depends on 3 inputs and so is sort of iterative. Will any of Excel's
functions let me do what I want?

Thanks in advance,
Derrick

 

[Shane Devenshire]

Hi,

Assume the top left corner of your table is A1 and you enter the
Item1
Material1
Style2
Size3

In G1:G4 then this will return the results you want

=SUMPRODUCT((B1:E1=G1)*(B2:E2=G2)*(B3:E3=G3)*(A4:A6=G4)*B4:E6)

 

[aaa]

Hi Shane, and thank you very much. The SUMPRODUCT function does the trick,
except for when a price is text, e.g. "Price on application", instead of a
numerical $ value. Is there a simple way to overcome this problem?

Cheers,
Derrick

 

[aaa]

Brilliant, thank you! Now that it works I'll try to figure out how!

 

[aaa]

Sorry Shane, I had already clicked yes on SMartin's post. Thanks for the
help guys and for the tip to use the evaluate formula feature to try to
understand the solution.

 

[Herbert Seidenberg]

Excel 2007.
Four ways to select an item in an array.
http://www.mediafire.com/file/wgzgigydmgn/Array_pick.xlsm