Picking values from a table....

Solved/Closed
Nimo212 Posts 1 Registration date Tuesday November 16, 2010 Status Member Last seen November 16, 2010 - Nov 16, 2010 at 01:03 AM
 Nimo212 - Nov 17, 2010 at 02:46 AM
Hello,

Need help.. somebody picks the cone size and energy from drop down list then excel fill in the value in another cell. e.g. pick cell A1 = 25 * 25 cone and B1 =10E... Excel gives 0.97 in cell C1

for the cone size and energy without matching values excel returns invalid entry. eg 25 * 25 and 15E


Cone\energy 6E 8E 10E 12E 15E 18E

5 cm 0.785 0.869 0.898 0.931 0.952 0.979
10 x 10 1.000 1.000 1.000 1.000 1.000 1.000
15 x 15 1.023 1.003 0.997 0.996 1.006 0.997
20 x 20 1.035 0.992 0.974 0.964 0.977 0.964
25 x 25 1.022 0.982 0.970 0.960 0.980 0.965
2 cm Lucite 0.519 0.663 0.794
3 cm Lucite 0.775 0.878 0.959
4 cm Lucite 0.935 0.989 1.021 0.980
5 cm Lucite 1.049 1.070 1.100 1.100 1.090 1.092
7 cm Stainless Steel 1.042 1.062 1.075 1.086 1.085 1.092
'


Tried using if but I can only nest 7 ifs. I got the drop down list, need the section part. Got problems with maintaining the table format. You can think of what I am trying to do like a math multiplication table. pick a number for the vertical and horizontal column and the it gives the product. The only difference in my case its not numbers its all mixed..

Thanks

1 response

This returns the value from the table.
=INDEX(D7:I16,MATCH(K3,B7:B16,0),MATCH(L3,D5:I5,0))

Range D7:I16 is the table.
Cell K3 contans the chosen Cone value (e.g.10x10)
Range B7:B16 is the list of Cone Values
Cell L3 contains the chosen energy value (e.g. 8E)
Range D5:I5 is the list of energy values
1
Thanks RayH

It worked just the way I wanted it to, didn't know about index and match in excel.

Thanks again.
0