Finding data through rows and columns

[cblack1]

I am trying to create a formula for some structural analysis but having trouble with what I think might be the Lookup function.

My column, for rows, basically runs from 40-300 in increments of 5-10. I have a row, for columns, at the top running from 5-40 in increments of 5.

What I wish to do is for example find the number that would sit within the row and column. See below.

1 2 3 4 5 6
1 2 4 6 8 9 12
2 1 2 3 4 5 7
3 5 6 4 99 7 8
4 4 5 6 8 2 1


So for example i want to find the number relating to number 3 column one, to 5 in the top row. This gives the answer of 99.

any comments will be much appreciated.

thanks

callum

[JBeaucaire]

That's an INDEX/MATCH/MATCH function.

Take a look at the formula in G1 of the attached file.

[cblack1]

Ok I think that i have that done. I have selected the data i want and what i want to match however an error code of value not available code is coming up. any ideas?

thanks for the fast reply

[JBeaucaire]

SHow your work. Post your resulting workbook so we can see the data and the formula you tried. Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook.

[cblack1]

Ok i think i have attached it. If you look there is a D/T value and a Le/Ry value. These are the numbers which in turn i wish to look up.

Sheet two is laid out clearer with different sizes of beams, where as sheet one is for manually entering different beam properties.


Thanks

[:) Sixthsense :)]

Ok i think i have attached it. If you look there is a D/T value and a Le/Ry value.

May be this...

In J4 cell

Please Login or Register  to view this content.

Drag the formula down

[JBeaucaire]

My formula in J4 would be:

=INDEX($X$3:$AG$37, MATCH(H4, $W$3:$W$37, 1), MATCH(D4, $X$2:$AG$2, 1))