lookup function

[buening]

Hello,
I found your wonderful forum, so hopefully I can get some help. I'm familiar with Lookup, Hlookup, and Vlookup. I have a situation where a particular column or row cannot be specified, as it is dependent on a cell. Attached is a spreadsheet with what I'm describing. Other than doing nested if statements with V or Hlookups, what can I use? I'm a structural engineer and the columns are sorted by a concrete beam size and the rows are sorted by strand patterns. Depending on the beam size and the strand pattern used, there is a particular "E" value that is used in my calculations. This "E" value, is the values in the table that range from 2.73 in one corner to 13.53 in the opposite corner. I would like to be able to input the beam size and the strand pattern and excel look up the E value based on my beam/strand input. Certain strand patterns are only available for each beam type, thus the reason for blank boxes in the table. I'm not sure if it's possible, but I'd like it to display a message if there is no value in the box. If not possible, displaying the blank value would suffice. Thanks for the help!!!

[Andy Pope]

Try this formula

=IF(SUMPRODUCT((B3:B19=C26)*(C2:N2=C25)*(C3:N19))=0,"Strand Pattern Not Available for this Beam Size",SUMPRODUCT((B3:B19=C26)*(C2:N2=C25)*(C3:N19)))

[buening]

Wow, thats not at all the formula I was expecting but it works!!! Thanks for the help, it is greatly apprecated.