Multiple Logical Tests Required

Hey,

I was wondering if someone could help me with the attached "Sample" document.

What I need is for the worksheet named "D&T - Formula", I need the table to be formulated with the correct information.

For example, if the drop down list in cell C2 is "Copper"

And the k value in cell G8, is higher than the value in cell G14 in the worksheet named "BS 74302011", but lower than the value in cell G15 in the worksheet named "BS 74302011". Row 15 of the "D&T - Formula" should be populated with the information from the worksheet named "BS 74302011" with the details of that of the lower value.
(See attached "Example-Complete").

I would then need to do this for the Aluminium and Steel options, using the drop down list in cell C2 of the "D&T - Formula" worksheet, but with the same outcome as the above.

The values in cell G8 of the "D&T - Formula" worksheet will continue to change.

Please let me know if further information is required and I apologise if I have not explained this very well!

Best regards,

Kate Weedon

see if this works for you

That's brilliant thanks rcm.

Is there any way to do it without the a,b,c...etc in row 17? (Just for neatness).

If not, it's no problem I'll just drop it to a lower row.

Thanks again,
Kate

Sure there is, I just included them to show how it was done
Just substitute the referenced cells with the letter in the formula

Thank you rcm!

My apologies for the questions, I'd just like to learn! , please can you let me know where you started?

Best regards,

Kate

I cleaned it up and checked for errors... also I had to recoded so your excell version could run it.

The key function is MATCH() and it is used in this instance as an approximator (the next lowest record)

In conjuntion with match() which results in the row# I used INDIRECT() which allows to reference an address by concatenating a string.

The error checking is done for clarity. If no record is found just leave the field blank.

Thank you so much!