Excel Equation Help

[DoesNotExcel]

Problem: Cell A2 can be either "Categ 1" or "Categ 2". If Categ 1 is selected, a drop down list is provided in B1. Based on selection in B1's drop down list, its relative value is shown below in B2.
If Categ 2 is selected in A2, then a completely different list is shown in B1, and based on that item selection in B1's list, a different value is shown below in B2.


[TABLE="width: 500"]
<tbody>[TR]
[TD]Equipment Cost[/TD]
[TD]B1-Selection[/TD]
[TD]C1-Selection[/TD]
[/TR]
[TR]
[TD]Dropdown - Cat 1 or Cat 2[/TD]
[TD]Cost based on Selection[/TD]
[TD]Cost Based on selection[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I have a drop down list located in A2. I can select up to two options. If I select Cat 1, then a list automatically populates in Cells B1, C1, and so on. Of the list, I choose item 4. After choosing this item, a price automatically populates below. Same thing happens for C1 with a different item chosen.

Now, if I select Cat 2, in A2, then a completely different list populates in Cells B1, C1 and so on. Now, when I choose an item from this list, the corresponding price auto populates as well.

This is my goal, the problem is, I can only get the price to automatically populate for one item. I'm using Vlookup. If I choose a different category, pick an item from another list, then I receive an "N/A". What equations do I need in order to make this happen. Please, from step one to step whatever. Not an excel major, just trying to make life easier in my sales department.

thanks in advance,
-DoesNotExcel

 

[Derek Brown]

The following MrExcel Podcast may help:

https://www.youtube.com/watch?v=zKzPWz0Fg6U