NumberCruncher311
New Member
- Joined
- Feb 5, 2013
- Messages
- 26
I need help with combining Index and Match. Can someone tell me why this isn't working? Below is an example of my worksheet. What I'm trying to do is create a dynamic Index/Worksheet combo that will help me identify the additional cost for the type of filling and size of cake. So, for example - I'd like the Index/Match formula to tell me that it's an additional $1.00 for a 6" round cake with Ganache frosting.
So that this can be dynamic, I've done the following (I should add that this is for VBA coding) ...
I have the following named ranges:
A1:F5 "FrostingAddOn"
A1:A5 "FrostingNames"
<tbody>
</tbody>
Here is the code I've been using and it doesn't work:
Note - the data is getting pulled from several combo boxes, I don't know how to simplify this any more than I have.
So that this can be dynamic, I've done the following (I should add that this is for VBA coding) ...
I have the following named ranges:
A1:F5 "FrostingAddOn"
A1:A5 "FrostingNames"
A | B | C | D | E | F | |
1 | _4Round | _6Round | _7Round | _8Round | _9Round | |
2 | Brown Sugar | .5 | .75 | 1.00 | 1.25 | 1.50 |
3 | Chocolate Hazelnut | .5 | .75 | 1.00 | 1.25 | 1.50 |
4 | Chocolate Peanut Butter | .6 | .8 | 1.25 | 1.5 | 1.75 |
5 | Ganache | .75 | 1.00 | 1.25 | 1.50 | 1.75 |
<tbody>
</tbody>
Here is the code I've been using and it doesn't work:
Note - the data is getting pulled from several combo boxes, I don't know how to simplify this any more than I have.
Code:
Dim Tier1 As String, AddOnCost As String
Dim Tier1AC As Long
If OneTier.Tier1Frost.Value = "Chocolate Hazelnut" Then
AddOnCost = "Chocolate Hazelnut"
ElseIf OneTier.Tier1Fost.Value="Chocolate Ganache" Then
AddOnCost = "Ganache"
End If
Tier1 = OneTier.Tier1Size.Value
Tier1AC = Application.WorksheetFunction.Index("FrostingAddOn", Application.WorksheetFunction.Match("AddOnCost", "FrostingNames", 0), Application.WorksheetFunction.Match(Tier1, "FrostingAddOn", 0))
MsgBox "Additional Cost Is: " & Tier1AC