Formula help

[Ztcollins]

[TABLE="width: 502"]
<tbody>[TR]
[TD]Wall[/TD]
[TD][/TD]
[TD]Wall Type[/TD]
[TD][/TD]
[TD]Unit[/TD]
[TD]Price[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]12' x 10"[/TD]
[TD][/TD]
[TD]Basement Wall[/TD]
[TD][/TD]
[TD]100[/TD]
[TD]$80.00[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Blue Text - Data from dropdown list selected by user. (Dropdown list data shown below)
Red Text - Price that i want the formula to select by referencing what selection is made in the dropdown list.

IE: If I select 1' x 6" in the dropdown list then in the price section i want $37.00 show up.

I have tried this using =IF(C6=List!B1,List!C1, "0") but when i try to expand on it to include the rest of the data it gets way to long and doesnt work.

Any and all help would be appreciated.

Thanks!

This is the data that i am using for reference.

[TABLE="width: 128"]
<tbody>[TR]
[TD="class: xl66, width: 64"]1' x 6"
[/TD]
[TD="class: xl67, width: 64, align: right"]$37.00
[/TD]
[/TR]
[TR]
[TD="class: xl66"]2' x 6"
[/TD]
[TD="class: xl67, align: right"]$42.00
[/TD]
[/TR]
[TR]
[TD="class: xl66"]3' x 6"[/TD]
[TD="class: xl67, align: right"]$46.00
[/TD]
[/TR]
[TR]
[TD="class: xl66"]4' x 6"[/TD]
[TD="class: xl67, align: right"]$51.00[/TD]
[/TR]
[TR]
[TD="class: xl66"]5' x 6"[/TD]
[TD="class: xl67, align: right"]$56.00[/TD]
[/TR]
[TR]
[TD="class: xl66"]6' x 6"[/TD]
[TD="class: xl67, align: right"]$60.00[/TD]
[/TR]
[TR]
[TD="class: xl66"]7' x 6"[/TD]
[TD="class: xl67, align: right"]$63.00[/TD]
[/TR]
[TR]
[TD="class: xl66"]8' x 6"[/TD]
[TD="class: xl67, align: right"]$65.00[/TD]
[/TR]
[TR]
[TD="class: xl66"]9' x 6"[/TD]
[TD="class: xl67, align: right"]$69.00[/TD]
[/TR]
[TR]
[TD="class: xl66"]10' x 6"[/TD]
[TD="class: xl67, align: right"]$72.00[/TD]
[/TR]
[TR]
[TD="class: xl66"]11' x 6"[/TD]
[TD="class: xl67, align: right"]$77.00[/TD]
[/TR]
[TR]
[TD="class: xl66"]12' x 6"[/TD]
[TD="class: xl67, align: right"]$84.00[/TD]
[/TR]
[TR]
[TD="class: xl66"]13' x 6"[/TD]
[TD="class: xl67, align: right"]$90.00[/TD]
[/TR]
[TR]
[TD="class: xl66"]14' x 6"[/TD]
[TD="class: xl67, align: right"]$96.00[/TD]
[/TR]
[TR]
[TD="class: xl66"]15' x 6"[/TD]
[TD="class: xl67, align: right"]$102.00[/TD]
[/TR]
[TR]
[TD]1' x 8"[/TD]
[TD="class: xl67, align: right"]$38.00[/TD]
[/TR]
[TR]
[TD]2' x 8"[/TD]
[TD="class: xl67, align: right"]$43.00[/TD]
[/TR]
[TR]
[TD="class: xl66"]3' x 8"[/TD]
[TD="class: xl67, align: right"]$47.00[/TD]
[/TR]
[TR]
[TD="class: xl66"]4' x 8"[/TD]
[TD="class: xl67, align: right"]$52.00[/TD]
[/TR]
[TR]
[TD="class: xl66"]5' x 8"[/TD]
[TD="class: xl67, align: right"]$57.00[/TD]
[/TR]
[TR]
[TD="class: xl66"]6' x 8"[/TD]
[TD="class: xl67, align: right"]$61.00[/TD]
[/TR]
[TR]
[TD="class: xl66"]7' x 8"[/TD]
[TD="class: xl67, align: right"]$65.00[/TD]
[/TR]
[TR]
[TD="class: xl66"]8' x 8"[/TD]
[TD="class: xl67, align: right"]$68.00[/TD]
[/TR]
[TR]
[TD="class: xl66"]9' x 8"[/TD]
[TD="class: xl67, align: right"]$72.00[/TD]
[/TR]
[TR]
[TD="class: xl66"]10' x 8"[/TD]
[TD="class: xl67, align: right"]$76.00
[/TD]
[/TR]
[TR]
[TD="class: xl66"]11' x 8"[/TD]
[TD="class: xl67, align: right"]$81.00[/TD]
[/TR]
[TR]
[TD="class: xl66"]12' x 8"[/TD]
[TD="class: xl67, align: right"]$88.00[/TD]
[/TR]
[TR]
[TD="class: xl66"]13' x 8"[/TD]
[TD="class: xl67, align: right"]$95.00[/TD]
[/TR]
[TR]
[TD="class: xl66"]14' x 8"[/TD]
[TD="class: xl67, align: right"]$101.00[/TD]
[/TR]
[TR]
[TD="class: xl66"]15' x 8"[/TD]
[TD="class: xl67, align: right"]$108.00[/TD]
[/TR]
[TR]
[TD="class: xl66"]1' x 10"[/TD]
[TD="class: xl67, align: right"]$39.00[/TD]
[/TR]
[TR]
[TD="class: xl66"]2' x 10"[/TD]
[TD="class: xl67, align: right"]$44.00[/TD]
[/TR]
[TR]
[TD="class: xl66"]3' x 10"[/TD]
[TD="class: xl67, align: right"]$48.00[/TD]
[/TR]
[TR]
[TD="class: xl66"]4' x 10"[/TD]
[TD="class: xl67, align: right"]$53.00[/TD]
[/TR]
[TR]
[TD="class: xl66"]5' x 10"[/TD]
[TD="class: xl67, align: right"]$58.00[/TD]
[/TR]
[TR]
[TD="class: xl66"]6' x 10"[/TD]
[TD="class: xl67, align: right"]$62.00[/TD]
[/TR]
[TR]
[TD="class: xl66"]7' x 10"[/TD]
[TD="class: xl67, align: right"]$67.00[/TD]
[/TR]
[TR]
[TD="class: xl66"]8' x 10"[/TD]
[TD="class: xl67, align: right"]$71.00[/TD]
[/TR]
[TR]
[TD="class: xl66"]9' x 10"[/TD]
[TD="class: xl67, align: right"]$75.00[/TD]
[/TR]
[TR]
[TD="class: xl66"]10' x 10"[/TD]
[TD="class: xl67, align: right"]$80.00[/TD]
[/TR]
[TR]
[TD="class: xl66"]11' x 10"[/TD]
[TD="class: xl67, align: right"]$85.00[/TD]
[/TR]
[TR]
[TD="class: xl66"]12' x 10"[/TD]
[TD="class: xl67, align: right"]$92.00[/TD]
[/TR]
[TR]
[TD="class: xl66"]13' x 10"[/TD]
[TD="class: xl67, align: right"]$100.00[/TD]
[/TR]
[TR]
[TD="class: xl66"]14' x 10"[/TD]
[TD="class: xl67, align: right"]$109.00[/TD]
[/TR]
[TR]
[TD="class: xl66"]15' x 10"[/TD]
[TD="class: xl67, align: right"]$119.00[/TD]
[/TR]
[TR]
[TD="class: xl66"]1' x 12"[/TD]
[TD="class: xl67, align: right"]$40.00[/TD]
[/TR]
[TR]
[TD="class: xl66"]2' x 12"[/TD]
[TD="class: xl67, align: right"]$45.00[/TD]
[/TR]
[TR]
[TD="class: xl66"]3' x 12"[/TD]
[TD="class: xl67, align: right"]$49.00[/TD]
[/TR]
[TR]
[TD="class: xl66"]4' x 12"[/TD]
[TD="class: xl67, align: right"]$54.00[/TD]
[/TR]
[TR]
[TD="class: xl65"]5' x 12"[/TD]
[TD="class: xl67, align: right"]$59.00[/TD]
[/TR]
[TR]
[TD="class: xl65"]6' x 12"[/TD]
[TD="class: xl67, align: right"]$63.00[/TD]
[/TR]
[TR]
[TD="class: xl65"]7' x 12"[/TD]
[TD="class: xl67, align: right"]$69.00[/TD]
[/TR]
[TR]
[TD="class: xl65"]8' x 12"[/TD]
[TD="class: xl67, align: right"]$74.00[/TD]
[/TR]
[TR]
[TD="class: xl65"]9' x 12"[/TD]
[TD="class: xl67, align: right"]$78.00[/TD]
[/TR]
[TR]
[TD="class: xl65"]10' x 12"[/TD]
[TD="class: xl67, align: right"]$84.00[/TD]
[/TR]
[TR]
[TD="class: xl65"]11' x 12"[/TD]
[TD="class: xl67, align: right"]$89.00[/TD]
[/TR]
[TR]
[TD="class: xl65"]12' x 12"[/TD]
[TD="class: xl67, align: right"]$96.00[/TD]
[/TR]
[TR]
[TD="class: xl65"]13' x 12"[/TD]
[TD="class: xl67, align: right"]$105.00[/TD]
[/TR]
[TR]
[TD="class: xl65"]14' x 12"[/TD]
[TD="class: xl67, align: right"]$117.00[/TD]
[/TR]
[TR]
[TD="class: xl65"]15' x 12"[/TD]
[TD="class: xl67, align: right"]$127.00[/TD]
[/TR]
</tbody>[/TABLE]

 

[daverm]

use an index and match formula.

In the cell you want the price to appear in enter the following formula:

=Index(List!$B$1:$B$118,match(C6,List!$C$1:$C$118,0))

Where:

List!$B$1:$B$118 = range of sizes
List!$C$1:$C$118 = range of prices
C6 = cell containing size selection

 

[anthonya2369]

Vlookup or Match would work for this...

vlookup(c6,List!B1:C100,2,false)

 

[Ztcollins]

Thanks for the info that you both provided. The =Index formula would not work for some reason but the Vlookup Formula works like a charm!!!! thanks!!!!

 

[daverm]

Thanks for the info that you both provided. The =Index formula would not work for some reason but the Vlookup Formula works like a charm!!!! thanks!!!!

Just realised why it doesn't work!

You would need to switch the two ranges around. i.e. =Index(List!$C$1:$C$118,match(C6,List!$B$1:$B$118,0))

Sorry!