Select value from table based upon two values 1

[StephenNapper]

Dear all am trying to create a cell to pull up nail shear strengths based upon nail diameter and timber grade.

Table included below.

Nail C14 C16/18/20 C24 TR26/C27/C30/35/40
Dia
2.7 32 249 258 274
3 36 296 306 326
3.4 41 364 377 400
3.8 46 438 453 481
4.2 50 516 534 567
4.6 55 600 620 659
5 60 689 712 756
5.5 66 806 833 885
6 72 930 962 1022
7 84 1200 1240 1318
8 96 1495 1546 1643

I know this is relatively easy as I did something similar years ago, but I can't remember how!! If it helsp I'll add columns for the individual grades, i.e. C16/18/20 C16, C18, C20.

Thanks in advance

Steve

 

[xlbo]

with individual columns for grades, have a look at acombination of the INDEX & MATCH formulae

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SteveNapper]

Geoff,

Thanks for the speedy response, but using the formulae

INDEX(Nail Dia Column Range,MATCH,Nail Dia,Strength of Nail Column for grade,0))

I can only see how one grade would work, can I combine this with mutiple IF's for the grades usign named ranges or the cell ranges / grade?

Steve

 

[xlbo]

ok - I have assumed your data is set up with Nail Diameters in 1 column - lets say column A, rows 2 to 100

I have assumed timber grades in row 1, columns 2 to 20 - one column per timber grade

Your Shear Strengths are therefore in range B2:T100

With the Nail Diameter ref in cell Z1 and the Timber Grade in Z2:

To get the row in which the Nail Diameter matches
=MATCH(Z1,$A$2:$A$100,0)
To get the column in which the Timber Grade matches
=MATCH(Z2,$B$1:$T$1,0)

To get the intersect of the column and row within your shear strengths:

=INDEX(B2:T100,Z1,Z2)

You may combine these formulae into 1:

=INDEX($B$2:$Z$100,MATCH(Z1,$A$2:$A$100,0),MATCH(Z2,$B$1:$T$1,0))


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SteveNapper]

Geoff,

THAMNKYOU SO MUCH. ABSOLUTELY FANTASTIC. Please take a star.

Have been looking at this for two days solid now.

Steve

 

[xlbo]

no worries

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question