Picking values from a table....

[Solved/Closed]
Report
Posts
1
Registration date
Tuesday November 16, 2010
Status
Member
Last seen
November 16, 2010
-
 Nimo212 -
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 reply

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
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

Thanks RayH

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

Thanks again.