VLookUP - tables, values

[EXCEL_Newbie87]

I have made a pricing template for my work - the basic spreadsheet relied upon a 'model' selected from a dropdown list (J1) this would then help to select a column in a table contained on a seperate sheet (Bonnet) for the price of the model, this would later be determined by dimensions and options input into the table. I had this working fine for 2 options (2 tables of values).

=IF([Toughened]="No",INDEX(Bonnet!$H$4:$L$15,MATCH([Area],Bonnet!$F$4:$F$15,1),MATCH($J$1,Bonnet!$H$3:$L$3,0)),INDEX(Bonnet!$H$21:$L$32,MATCH([Area],Bonnet!$F$21:$F$32,1),MATCH($J$1,Bonnet!$H$20:$L$20,0)))

I now have to add another 2 options (a futher 2 tables of values), but the parameters in the new additional tables are not identical to the existing tables - which is why it doesnt seem to work by just doubling like lookup formula and changing the ranges..

Any help or ideas about the best way to go about this would be great!

 

[Andrew Poulsom]

What do you mean by "the parameters in the new additional tables are not identical to the existing tables" exactly? What other conditions do you want to add and what data do you want to access with what parameters?

 

[EXCEL_Newbie87]

Apologies, Im not explaining myself very well, below is one of the tables used F3 to L15

=IF([Toughened]="No",INDEX(Bonnet!$H$4:$L$15,MATCH([Area],Bonnet!$F$4:$F$15,1),MATCH($J$1,Bonnet!$H$3:$L$3,0)),INDEX(Bonnet!$H$21:$L$32,MATCH([Area],Bonnet!$F$21:$F$32,1),MATCH($J$1,Bonnet!$H$20:$L$20,0)))

[TABLE="width: 528"]
<colgroup><col><col><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD]Size[/TD]
[TD]Band[/TD]
[TD]S_3[/TD]
[TD]S_4[/TD]
[TD]S_5[/TD]
[TD]S_6[/TD]
[TD]S_8[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD]<0.3[/TD]
[TD="align: right"]32.97[/TD]
[TD="align: right"]36.76[/TD]
[TD="align: right"]39.03[/TD]
[TD="align: right"]44.8[/TD]
[TD="align: right"]52.84[/TD]
[/TR]
[TR]
[TD="align: right"]0.3[/TD]
[TD]<0.4[/TD]
[TD="align: right"]39.54[/TD]
[TD="align: right"]44.8[/TD]
[TD="align: right"]48.23[/TD]
[TD="align: right"]55.11[/TD]
[TD="align: right"]65.42[/TD]
[/TR]
[TR]
[TD="align: right"]0.4[/TD]
[TD]<0.5[/TD]
[TD="align: right"]46.91[/TD]
[TD="align: right"]52.78[/TD]
[TD="align: right"]56.23[/TD]
[TD="align: right"]66.59[/TD]
[TD="align: right"]80.4[/TD]
[/TR]
[TR]
[TD="align: right"]0.5[/TD]
[TD]<0.6[/TD]
[TD="align: right"]59.89[/TD]
[TD="align: right"]66.53[/TD]
[TD="align: right"]68.86[/TD]
[TD="align: right"]80.33[/TD]
[TD="align: right"]94.13[/TD]
[/TR]
[TR]
[TD="align: right"]0.6[/TD]
[TD]<0.7[/TD]
[TD="align: right"]65.09[/TD]
[TD="align: right"]72.3[/TD]
[TD="align: right"]79.23[/TD]
[TD="align: right"]95.25[/TD]
[TD="align: right"]118.2[/TD]
[/TR]
[TR]
[TD="align: right"]0.7[/TD]
[TD]<0.8[/TD]
[TD="align: right"]71.13[/TD]
[TD="align: right"]79.23[/TD]
[TD="align: right"]86.1[/TD]
[TD="align: right"]104.45[/TD]
[TD="align: right"]129.67[/TD]
[/TR]
[TR]
[TD="align: right"]0.8[/TD]
[TD]<0.9[/TD]
[TD="align: right"]79.08[/TD]
[TD="align: right"]88.38[/TD]
[TD="align: right"]95.25[/TD]
[TD="align: right"]117.09[/TD]
[TD="align: right"]145.8[/TD]
[/TR]
[TR]
[TD="align: right"]0.9[/TD]
[TD]<1.0[/TD]
[TD="align: right"]83.85[/TD]
[TD="align: right"]95.25[/TD]
[TD="align: right"]103.28[/TD]
[TD="align: right"]125.07[/TD]
[TD="align: right"]154.89[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]<1.25[/TD]
[TD="align: right"]94.4[/TD]
[TD="align: right"]109.05[/TD]
[TD="align: right"]118.19[/TD]
[TD="align: right"]136.61[/TD]
[TD="align: right"]164.17[/TD]
[/TR]
[TR]
[TD="align: right"]1.25[/TD]
[TD]<1.5[/TD]
[TD="align: right"]103.73[/TD]
[TD="align: right"]122.79[/TD]
[TD="align: right"]134.27[/TD]
[TD="align: right"]148.02[/TD]
[TD="align: right"]173.25[/TD]
[/TR]
[TR]
[TD="align: right"]1.5[/TD]
[TD]<2.4[/TD]
[TD="align: right"]138.87[/TD]
[TD="align: right"]138.87[/TD]
[TD="align: right"]180.42[/TD]
[TD="align: right"]213.38[/TD]
[TD="align: right"]260.07[/TD]
[/TR]
[TR]
[TD="align: right"]2.4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

so the value selected is depended on a value that is put into a size column in the spreadsheet - where it is selects a 'band' <0.3 , <0.4 etc

The new tables which I want to search have more 'bands'

[TABLE="width: 478"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]Size[/TD]
[TD]Band[/TD]
[TD]IM 19[/TD]
[TD]IM 15 [/TD]
[TD]IM 13 [/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD]<0.2[/TD]
[TD="align: right"]20.40[/TD]
[TD="align: right"]25.20[/TD]
[TD="align: right"]30.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]0.2[/TD]
[TD]<0.25[/TD]
[TD="align: right"]22.80[/TD]
[TD="align: right"]27.60[/TD]
[TD="align: right"]32.40[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]0.25[/TD]
[TD]<0.3[/TD]
[TD="align: right"]26.40[/TD]
[TD="align: right"]31.20[/TD]
[TD="align: right"]36.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]0.3[/TD]
[TD]<0.4[/TD]
[TD="align: right"]31.40[/TD]
[TD="align: right"]36.12[/TD]
[TD="align: right"]47.02[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]0.4[/TD]
[TD]<0.5[/TD]
[TD="align: right"]35.17[/TD]
[TD="align: right"]40.46[/TD]
[TD="align: right"]52.64[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]0.5[/TD]
[TD]<0.6[/TD]
[TD="align: right"]39.40[/TD]
[TD="align: right"]45.31[/TD]
[TD="align: right"]58.97[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]0.6[/TD]
[TD]<0.7[/TD]
[TD="align: right"]44.15[/TD]
[TD="align: right"]50.75[/TD]
[TD="align: right"]66.05[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]0.7[/TD]
[TD]<0.8[/TD]
[TD="align: right"]49.44[/TD]
[TD="align: right"]56.84[/TD]
[TD="align: right"]73.97[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]0.8[/TD]
[TD]<0.9[/TD]
[TD="align: right"]55.37[/TD]
[TD="align: right"]63.66[/TD]
[TD="align: right"]82.85[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]0.9[/TD]
[TD]<1.0[/TD]
[TD="align: right"]62.03[/TD]
[TD="align: right"]71.30[/TD]
[TD="align: right"]92.78[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD]<1.25[/TD]
[TD="align: right"]77.52[/TD]
[TD="align: right"]89.14[/TD]
[TD="align: right"]115.99[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]1.25[/TD]
[TD]<1.50[/TD]
[TD="align: right"]93.04[/TD]
[TD="align: right"]106.96[/TD]
[TD="align: right"]138.71[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]1.5[/TD]
[TD]<2.00[/TD]
[TD="align: right"]124.06[/TD]
[TD="align: right"]142.61[/TD]
[TD="align: right"]185.57[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]



[/TD]
[/TR]
</tbody>[/TABLE]
I think this is what caused my attempts to get it working to fail.. is there a way where I could show / attach the file as a lot of the formula rely on results of other formula and I could possibly be barking up the wrong tree about where the solution lies.. any insights though would be more than appreciated!

 

[Andrew Poulsom]

Sorry, I don't see how having more rows in the lookup table would make any diffeence. Did you try moifying your formula? If so what was it?

 

[EXCEL_Newbie87]

Well there are two product types that relate to each of the two tables - so I tried to add an IF ELSE Statement to the below formula and then duplicate it and change the MATCH ranges for the two new tables, but that didnt seem to work..


=IF([Toughened]="No",INDEX(Bonnet!$H$4:$L$15,MATCH([Area],Bonnet!$F$4:$F$15,1),MATCH($J$1,Bonnet!$H$3:$L$3,0)),INDEX(Bonnet!$H$21:$L$32,MATCH([Area],Bonnet!$F$21:$F$32,1),MATCH($J$1,Bonnet!$H$20:$L$20,0)))

 

[Andrew Poulsom]

That's your original formula. What other conditions do you want to add?