Complex LOOKUP problem

[south_91]

Hi, have lurked this forum for a while and found it extremely helpful.

Have come across a problem I can't find an answer to by browsing old threads so have joined up, hello to all

I want to extract a value from a table which I have added to this post as an attachment.

The table is for precast concrete flooring slabs. Firstly there will be a value for Load (say 1.25). I need to select the row based upon this. The Load value in the selected row must be the upper bound of the 2 values between which the inputted value falls (i.e. row 7, load = 1.5 in this case). I am then ultimately looking for the self weight of the required slab based on the span (say 11m giving a self weight of 3.3kn/m2 as the upper bound must be used).

Apologies for how poorly worded this is but hopefully you get the idea, I've had a long and frustrating day Also sorry if this is in the wrong section of the forum.

Thanks

[Pete_UK]

Welcome to the Forum - no need to lurk anymore !!

A picture is not much use to work with. Can you attach the actual workbook (in a similar way as attaching the .jpg file).

Pete

[south_91]

Have attached a workbook containing the table and input values. The original file is rather unnecessarily large I think

Thank you

[FDibbins]

This is a bit long-winded, bit give it a try...
=INDEX($C$5:$I$12,MATCH(VLOOKUP(F17,$B$5:$B$12,1,1),B5:B12,1)+1,1+IFERROR(MATCH(HLOOKUP(C17,C3:I3,1,1),$C$3:$I$3,0),0))

[south_91]

Hmm looks to be along the right lines (vlookup, match etc) but this returns a value of 7.5 whereas it should be 3.3 (self weight) using the input values as they are.

You have used the unit depth values (C3:I3) when actually they are inconsequential in this case

Thanks again

[south_91]

I have now solved this using a very longwinded combination of IF, INDEX and MATCH functions. It works but isn't particularly elegant!

Thanks all