Index and Match Problem

N

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"


[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]_4Round[/TD]
[TD]_6Round[/TD]
[TD]_7Round[/TD]
[TD]_8Round[/TD]
[TD]_9Round[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Brown Sugar[/TD]
[TD].5[/TD]
[TD].75[/TD]
[TD]1.00[/TD]
[TD]1.25[/TD]
[TD]1.50[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Chocolate Hazelnut[/TD]
[TD].5[/TD]
[TD].75[/TD]
[TD]1.00[/TD]
[TD]1.25[/TD]
[TD]1.50[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Chocolate Peanut Butter[/TD]
[TD].6[/TD]
[TD].8[/TD]
[TD]1.25[/TD]
[TD]1.5[/TD]
[TD]1.75[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Ganache[/TD]
[TD].75[/TD]
[TD]1.00[/TD]
[TD]1.25[/TD]
[TD]1.50[/TD]
[TD]1.75[/TD]
[/TR]
</tbody>[/TABLE]

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
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Sort by date Sort by votes
Heres an example:

Code: 
Set Rng = Range("FrostingAddOn")
myRow = Application.Match("Ganache", Rng.Columns(1), 0)
myColumn = Application.Match("_6Round", Rng.Rows(1), 0)

If Not IsError(myRow) And Not IsError(myColumn) Then myVal = Rng.Cells(myRow, myColumn).Value
 
Last edited:
Upvote 0
Thank you! This works almost perfectly. How to I get the exact number instead of rounding up or down. Many of the cells have values like "0.18" in which case it is returning a 0.

steve the fish said:
Heres an example:

Code: 
Set Rng = Range("FrostingAddOn")
myRow = Application.Match("Ganache", Rng.Columns(1), 0)
myColumn = Application.Match("_6Round", Rng.Rows(1), 0)

If Not IsError(myRow) And Not IsError(myColumn) Then myVal = Rng.Cells(myRow, myColumn).Value
Click to expand...
 
Upvote 0
You will have dimensioned your variable as an integer I'd have thought. Change to double.
 
Upvote 0
steve the fish said:
You will have dimensioned your variable as an integer I'd have thought. Change to double.
Click to expand...

Okay, now I'm having a different problem. You'll see I'm trying to add up the additional costs associated with different ingredients for a multi-tiered cake. I've re-named myRow and myColumn to "myRow1" and 'myColumn1" for Tier 1 and "myRow2" and 'myColumn2" for Tier 2. I keep getting an "Object Required" error message. See my code below ...

Code: 
Sub Frosting_Two()


Dim Tier1 As String, Tier2 As String
Dim Tier1AC As Double, Tier2AC As Double
Dim Tier1R As Double, Tier2R As Double
Dim rng1 As Range, rng2 As Range
Dim AddOnCost1 As String, AddOnCost2 As String
      
Sheet1.Range("B9").Value = TwoTiers.Tier1Frost.Value & " / " & TwoTiers.Tier2Frost.Value
      
'TIER 1
If TwoTiers.Tier1Frost.Value = "Chocolate Buttercream" Or TwoTiers.Tier1Frost.Value = "Vanilla Buttercream" Or TwoTiers.Tier1Frost.Value = "" Or TwoTiers.Dummy1.Value = True Then
    Tier1AC = 0
    Tier1R = 0
Else
    If TwoTiers.Tier1Frost.Value = "Brown Sugar Buttercream" Then
        AddOnCost1 = "Brown Sugar BC"
    ElseIf TwoTiers.Tier1Frost.Value = "Chocolate Ganache" Then
        AddOnCost1 = "Ganache"
    ElseIf TwoTiers.Tier1Frost.Value = "Chocolate PB Buttercream" Then
        AddOnCost1 = "Chocolate Peanut Butter BC"
    ElseIf TwoTiers.Tier1Frost.Value = "Lemon Buttercream" Then
        AddOnCost1 = "Lemon BC"
    ElseIf TwoTiers.Tier1Frost.Value = "Nutella Buttercream" Then
        AddOnCost1 = "Nutella BC"
    ElseIf TwoTiers.Tier1Frost.Value = "Peanut Butter Buttercream" Then
        AddOnCost1 = "Peanut Butter BC"
    ElseIf TwoTiers.Tier1Frost.Value = "Salted Caramel Buttercream" Then
        AddOnCost1 = "Salted Caramel BC"
    ElseIf TwoTiers.Tier1Frost.Value = "Strawberry Buttercream" Then
        AddOnCost1 = "Strawberry BC"
    End If


'TIER 1 COSTS & RETAIL
Set rng1 = Range("FrostingAddOn")
Tier1 = TwoTiers.Tier1Size.Value
Set myRow1 = Application.Match(AddOnCost1, Rng.Columns(1), 0)
Set myColumn1 = Application.Match(Tier1, Rng.Rows(1), 0)
    If Not IsError(myRow1) And Not IsError(myColumn1) Then
        If TwoTiers.Three1.Value = True Or TwoTiers.Double1.Value = True Then
            Tier1AC = Rng.Cells(myRow1, myColumn1).Value * 2
        ElseIf TwoTiers.Dummy1.Value = True Then
            Tier1AC = 0
        ElseIf TwoTiers.Three1.Value = False And TwoTiers.Double1.Value = False And TwoTiers.Dummy1.Value = False Then
            Tier1AC = Rng.Cells(myRow1, myColumn1).Value
        End If
    End If


End If


'TIER 2
If TwoTiers.Tier2Frost.Value = "Chocolate Buttercream" Or TwoTiers.Tier2Frost.Value = "Vanilla Buttercream" Or TwoTiers.Tier2Frost.Value = "" Or TwoTiers.Dummy2.Value = True Then
    Tier2AC = 0
    Tier2R = 0
Else
    If TwoTiers.Tier2Frost.Value = "Brown Sugar Buttercream" Then
        AddOnCost2 = "Brown Sugar BC"
    ElseIf TwoTiers.Tier2Frost.Value = "Chocolate Ganache" Then
        AddOnCost2 = "Ganache"
    ElseIf TwoTiers.Tier2Frost.Value = "Chocolate PB Buttercream" Then
        AddOnCost2 = "Chocolate Peanut Butter BC"
    ElseIf TwoTiers.Tier2Frost.Value = "Lemon Buttercream" Then
        AddOnCost2 = "Lemon BC"
    ElseIf TwoTiers.Tier2Frost.Value = "Nutella Buttercream" Then
        AddOnCost2 = "Nutella BC"
    ElseIf TwoTiers.Tier2Frost.Value = "Peanut Butter Buttercream" Then
        AddOnCost2 = "Peanut Butter BC"
    ElseIf TwoTiers.Tier2Frost.Value = "Salted Caramel Buttercream" Then
        AddOnCost2 = "Salted Caramel BC"
    ElseIf TwoTiers.Tier2Frost.Value = "Strawberry Buttercream" Then
        AddOnCost2 = "Strawberry BC"
    End If


'TIER 2 COSTS & RETAIL
Set rng2 = Range("FrostingAddOn")
Tier2 = TwoTiers.Tier2Size.Value
Set myRow2 = Application.Match(AddOnCost2, Rng.Columns(1), 0)
Set myColumn2 = Application.Match(Tier2, Rng.Rows(1), 0)
    If Not IsError(myRow2) And Not IsError(myColumn2) Then
        If TwoTiers.Three2.Value = True Or TwoTiers.Double2.Value = True Then
            Tier2AC = Rng.Cells(myRow2, myColumn2).Value * 2
        ElseIf TwoTiers.Dummy2.Value = True Then
            Tier2AC = 0
        ElseIf TwoTiers.Three2.Value = False And TwoTiers.Double2.Value = False And TwoTiers.Dummy2.Value = False Then
            Tier2AC = Rng.Cells(myRow2, myColumn2).Value
        End If
    End If


End If


Sheet1.Range("H9").Value = Tier1AC + Tier2AC
Sheet1.Range("J9").Value = Tier1R + Tier2R
 
Upvote 0
You 'set' objects such as ranges not strings. You do not need the set in 'Set myRow1' so delete the set.
 
Upvote 0
steve the fish said:
You 'set' objects such as ranges not strings. You do not need the set in 'Set myRow1' so delete the set.
Click to expand...

Okay, I did that. I'm still getting "Runtime error 424. Object Required." The debugger highlights the line: myRow1 = Application.Match(AddOnCost1, Rng.Columns(1), 0)

My code is below ...

Code: 
Sub Frosting_Two()


Dim Tier1 As String, Tier2 As String
Dim Tier1AC As Double, Tier2AC As Double
Dim Tier1R As Double, Tier2R As Double
Dim rng1 As Range, rng2 As Range
Dim AddOnCost1 As String, AddOnCost2 As String
      
Sheet1.Range("B9").Value = TwoTiers.Tier1Frost.Value & " / " & TwoTiers.Tier2Frost.Value
      
'TIER 1
If TwoTiers.Tier1Frost.Value = "Chocolate Buttercream" Or TwoTiers.Tier1Frost.Value = "Vanilla Buttercream" Or TwoTiers.Tier1Frost.Value = "" Or TwoTiers.Dummy1.Value = True Then
    Tier1AC = 0
    Tier1R = 0
Else
    If TwoTiers.Tier1Frost.Value = "Brown Sugar Buttercream" Then
        AddOnCost1 = "Brown Sugar BC"
    ElseIf TwoTiers.Tier1Frost.Value = "Chocolate Ganache" Then
        AddOnCost1 = "Ganache"
    ElseIf TwoTiers.Tier1Frost.Value = "Chocolate PB Buttercream" Then
        AddOnCost1 = "Chocolate Peanut Butter BC"
    ElseIf TwoTiers.Tier1Frost.Value = "Lemon Buttercream" Then
        AddOnCost1 = "Lemon BC"
    ElseIf TwoTiers.Tier1Frost.Value = "Nutella Buttercream" Then
        AddOnCost1 = "Nutella BC"
    ElseIf TwoTiers.Tier1Frost.Value = "Peanut Butter Buttercream" Then
        AddOnCost1 = "Peanut Butter BC"
    ElseIf TwoTiers.Tier1Frost.Value = "Salted Caramel Buttercream" Then
        AddOnCost1 = "Salted Caramel BC"
    ElseIf TwoTiers.Tier1Frost.Value = "Strawberry Buttercream" Then
        AddOnCost1 = "Strawberry BC"
    End If


'TIER 1 COSTS & RETAIL
Set rng1 = Range("FrostingAddOn")
Tier1 = TwoTiers.Tier1Size.Value
myRow1 = Application.Match(AddOnCost1, Rng.Columns(1), 0)
myColumn1 = Application.Match(Tier1, Rng.Rows(1), 0)
    If Not IsError(myRow1) And Not IsError(myColumn1) Then
        If TwoTiers.Three1.Value = True Or TwoTiers.Double1.Value = True Then
            Tier1AC = Rng.Cells(myRow1, myColumn1).Value * 2
        ElseIf TwoTiers.Dummy1.Value = True Then
            Tier1AC = 0
        ElseIf TwoTiers.Three1.Value = False And TwoTiers.Double1.Value = False And TwoTiers.Dummy1.Value = False Then
            Tier1AC = Rng.Cells(myRow1, myColumn1).Value
        End If
    End If


End If


'TIER 2
If TwoTiers.Tier2Frost.Value = "Chocolate Buttercream" Or TwoTiers.Tier2Frost.Value = "Vanilla Buttercream" Or TwoTiers.Tier2Frost.Value = "" Or TwoTiers.Dummy2.Value = True Then
    Tier2AC = 0
    Tier2R = 0
Else
    If TwoTiers.Tier2Frost.Value = "Brown Sugar Buttercream" Then
        AddOnCost2 = "Brown Sugar BC"
    ElseIf TwoTiers.Tier2Frost.Value = "Chocolate Ganache" Then
        AddOnCost2 = "Ganache"
    ElseIf TwoTiers.Tier2Frost.Value = "Chocolate PB Buttercream" Then
        AddOnCost2 = "Chocolate Peanut Butter BC"
    ElseIf TwoTiers.Tier2Frost.Value = "Lemon Buttercream" Then
        AddOnCost2 = "Lemon BC"
    ElseIf TwoTiers.Tier2Frost.Value = "Nutella Buttercream" Then
        AddOnCost2 = "Nutella BC"
    ElseIf TwoTiers.Tier2Frost.Value = "Peanut Butter Buttercream" Then
        AddOnCost2 = "Peanut Butter BC"
    ElseIf TwoTiers.Tier2Frost.Value = "Salted Caramel Buttercream" Then
        AddOnCost2 = "Salted Caramel BC"
    ElseIf TwoTiers.Tier2Frost.Value = "Strawberry Buttercream" Then
        AddOnCost2 = "Strawberry BC"
    End If


'TIER 2 COSTS & RETAIL
Set rng2 = Range("FrostingAddOn")
Tier2 = TwoTiers.Tier2Size.Value
myRow2 = Application.Match(AddOnCost2, Rng.Columns(1), 0)
myColumn2 = Application.Match(Tier2, Rng.Rows(1), 0)
    If Not IsError(myRow2) And Not IsError(myColumn2) Then
        If TwoTiers.Three2.Value = True Or TwoTiers.Double2.Value = True Then
            Tier2AC = Rng.Cells(myRow2, myColumn2).Value * 2
        ElseIf TwoTiers.Dummy2.Value = True Then
            Tier2AC = 0
        ElseIf TwoTiers.Three2.Value = False And TwoTiers.Double2.Value = False And TwoTiers.Dummy2.Value = False Then
            Tier2AC = Rng.Cells(myRow2, myColumn2).Value
        End If
    End If


End If


Sheet1.Range("H9").Value = Tier1AC + Tier2AC
Tier1R = Tier1AC * 3
Tier2R = Tier2AC * 3
Sheet1.Range("J9").Value = Tier1R + Tier2R



Range("H9").NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
Range("J9").NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
Call Filling_Two
End Sub
 
Upvote 0
This isn't to address the immediate problem but there is too much static data in your code and it's making it very difficult to debug.

You could create a table in your sheet that maps TwoTiersTier1Frost.Value to the AddOnCost, name that entire table "AddOnCost" and use in code, i.e.
Code: 
AddOnCost1 = Application.VLOOKUP(TwoTiers.Tier1Frost.Value, Range("AddOnCost"), 2, 0)

Which would reduce at least 16 lines of code to 1

I suggest simplifying your code, creating a sheet called (e.g.) Settings and putting these sorts of data maps on this sheet, naming those ranges, using those named ranges in the code and then resolving index - match problem
 
Last edited:
Upvote 0
I figured it out!!! I hadn't changed the names of the ranges within the match formula. I had them as "rng" instead of "rng1" and "rng2". Working beautifully now.

NumberCruncher311 said:
Okay, I did that. I'm still getting "Runtime error 424. Object Required." The debugger highlights the line: myRow1 = Application.Match(AddOnCost1, Rng.Columns(1), 0)

My code is below ...

Code: 
Sub Frosting_Two()


Dim Tier1 As String, Tier2 As String
Dim Tier1AC As Double, Tier2AC As Double
Dim Tier1R As Double, Tier2R As Double
Dim rng1 As Range, rng2 As Range
Dim AddOnCost1 As String, AddOnCost2 As String
      
Sheet1.Range("B9").Value = TwoTiers.Tier1Frost.Value & " / " & TwoTiers.Tier2Frost.Value
      
'TIER 1
If TwoTiers.Tier1Frost.Value = "Chocolate Buttercream" Or TwoTiers.Tier1Frost.Value = "Vanilla Buttercream" Or TwoTiers.Tier1Frost.Value = "" Or TwoTiers.Dummy1.Value = True Then
    Tier1AC = 0
    Tier1R = 0
Else
    If TwoTiers.Tier1Frost.Value = "Brown Sugar Buttercream" Then
        AddOnCost1 = "Brown Sugar BC"
    ElseIf TwoTiers.Tier1Frost.Value = "Chocolate Ganache" Then
        AddOnCost1 = "Ganache"
    ElseIf TwoTiers.Tier1Frost.Value = "Chocolate PB Buttercream" Then
        AddOnCost1 = "Chocolate Peanut Butter BC"
    ElseIf TwoTiers.Tier1Frost.Value = "Lemon Buttercream" Then
        AddOnCost1 = "Lemon BC"
    ElseIf TwoTiers.Tier1Frost.Value = "Nutella Buttercream" Then
        AddOnCost1 = "Nutella BC"
    ElseIf TwoTiers.Tier1Frost.Value = "Peanut Butter Buttercream" Then
        AddOnCost1 = "Peanut Butter BC"
    ElseIf TwoTiers.Tier1Frost.Value = "Salted Caramel Buttercream" Then
        AddOnCost1 = "Salted Caramel BC"
    ElseIf TwoTiers.Tier1Frost.Value = "Strawberry Buttercream" Then
        AddOnCost1 = "Strawberry BC"
    End If


'TIER 1 COSTS & RETAIL
Set rng1 = Range("FrostingAddOn")
Tier1 = TwoTiers.Tier1Size.Value
myRow1 = Application.Match(AddOnCost1, Rng.Columns(1), 0)
myColumn1 = Application.Match(Tier1, Rng.Rows(1), 0)
    If Not IsError(myRow1) And Not IsError(myColumn1) Then
        If TwoTiers.Three1.Value = True Or TwoTiers.Double1.Value = True Then
            Tier1AC = Rng.Cells(myRow1, myColumn1).Value * 2
        ElseIf TwoTiers.Dummy1.Value = True Then
            Tier1AC = 0
        ElseIf TwoTiers.Three1.Value = False And TwoTiers.Double1.Value = False And TwoTiers.Dummy1.Value = False Then
            Tier1AC = Rng.Cells(myRow1, myColumn1).Value
        End If
    End If


End If


'TIER 2
If TwoTiers.Tier2Frost.Value = "Chocolate Buttercream" Or TwoTiers.Tier2Frost.Value = "Vanilla Buttercream" Or TwoTiers.Tier2Frost.Value = "" Or TwoTiers.Dummy2.Value = True Then
    Tier2AC = 0
    Tier2R = 0
Else
    If TwoTiers.Tier2Frost.Value = "Brown Sugar Buttercream" Then
        AddOnCost2 = "Brown Sugar BC"
    ElseIf TwoTiers.Tier2Frost.Value = "Chocolate Ganache" Then
        AddOnCost2 = "Ganache"
    ElseIf TwoTiers.Tier2Frost.Value = "Chocolate PB Buttercream" Then
        AddOnCost2 = "Chocolate Peanut Butter BC"
    ElseIf TwoTiers.Tier2Frost.Value = "Lemon Buttercream" Then
        AddOnCost2 = "Lemon BC"
    ElseIf TwoTiers.Tier2Frost.Value = "Nutella Buttercream" Then
        AddOnCost2 = "Nutella BC"
    ElseIf TwoTiers.Tier2Frost.Value = "Peanut Butter Buttercream" Then
        AddOnCost2 = "Peanut Butter BC"
    ElseIf TwoTiers.Tier2Frost.Value = "Salted Caramel Buttercream" Then
        AddOnCost2 = "Salted Caramel BC"
    ElseIf TwoTiers.Tier2Frost.Value = "Strawberry Buttercream" Then
        AddOnCost2 = "Strawberry BC"
    End If


'TIER 2 COSTS & RETAIL
Set rng2 = Range("FrostingAddOn")
Tier2 = TwoTiers.Tier2Size.Value
myRow2 = Application.Match(AddOnCost2, Rng.Columns(1), 0)
myColumn2 = Application.Match(Tier2, Rng.Rows(1), 0)
    If Not IsError(myRow2) And Not IsError(myColumn2) Then
        If TwoTiers.Three2.Value = True Or TwoTiers.Double2.Value = True Then
            Tier2AC = Rng.Cells(myRow2, myColumn2).Value * 2
        ElseIf TwoTiers.Dummy2.Value = True Then
            Tier2AC = 0
        ElseIf TwoTiers.Three2.Value = False And TwoTiers.Double2.Value = False And TwoTiers.Dummy2.Value = False Then
            Tier2AC = Rng.Cells(myRow2, myColumn2).Value
        End If
    End If


End If


Sheet1.Range("H9").Value = Tier1AC + Tier2AC
Tier1R = Tier1AC * 3
Tier2R = Tier2AC * 3
Sheet1.Range("J9").Value = Tier1R + Tier2R



Range("H9").NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
Range("J9").NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
Call Filling_Two
End Sub
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
Index and Match for date range
Replies
4
Views
575
mmhill
M
L
  • Question Question
Array Formula in Data Validation
Replies
1
Views
848
ExcelGzh
ExcelGzh

Forum statistics

Threads
1,223,950
Messages
6,175,582
Members
452,653
Latest member
craigje92

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top