Index/Match VS VLOOKUP 2

[joel009]

I finally got a VLOOKUP to work:
=VLOOKUP(D4,PREVIOUS!D4:AN115,37,FALSE)
This brings in the correct value.

But since I have heard the Index and Match combined is a better method I am trying to figure out why this does not work:

=INDEX(PREVIOUS!A4:AN115,MATCH(GE!D4,PREVIOUS!D4115,0),PREVIOUS!AN4:AN115)

It's on the same sheets, same data.

Also, how do I increment only the compare cell with an auto fill? When I copy down the column all the Row/Column values increase as well as the compare cell.



Joel

 

[SkipVought]

I just posted that, didn't you catch it?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[byundt]

Joel,
Here is how INDEX works:
=INDEX(Range of cells, row within that range, column within that range) returns a single value
If the row or column exceed the number of rows or columns within that range, then INDEX returns an error value.

In your most recent formula, the MATCH is returning the row index and the 40 is the column index. But the range of cells is only a single column: D4115. So INDEX returns an error.

Brad

 

[joel009]

Hi Skip - Thanks for sticking with me on this. Your Post:

Since you reference ONE COLUMN (D) in INDEX...

=INDEX(PREVIOUS!AN4:AN115,MATCH(GE!D4,PREVIOUS!D4115,0),1)


If you reference a multicolumn reference range, then you must supply the column number relative to the multicolumn reference range.

40 is column AN in the reference A:AN.

From what I see you would return the column next to column AN but the column to match on is in .....

Ahhhh, OK, I might see it now. AN4:AN115 is the return range or the data being sought. And the match contains the 2 sheet column definitions to do the match. So the 1 is the return column indicator. Seems to get shorter and sweeter.

Gleening that from =INDEX(reference, MATCH(lookup_value, lookup_array, match_type)
, column_num) has been difficult.

No Skip, I did not catch the change. Let me look a little more at this. Thank you! and Brad too!




Joel

 

[SkipVought]

| A  | B  | C  | D  | E  |[b]
+----+----+----+----+----+
|ValZ|NAME|ValA|ValB|ValC|[/b]
+----+----+----+----+----+
|x   |skip|is  |a   |nerd|
+----+----+----+----+----+
|z   |joel|some|cool|dude|
+----+----+----+----+----+

Forumla 1:
[tt]
=INDEX(B:E,MATCH("joel",B:B,0),4)
[/tt]
returns dude.


Forumla 2:
[tt]
=INDEX(E:E,MATCH("joel",B:B,0),1)
[/tt]
returns dude.

Forumla 3 using named ranges based on heading rows:
[tt]
=INDEX(ValC,MATCH("joel",NAME,0),1)
[/tt]
returns dude.

More than often the return column is to the LEFT of the lookup column in many of my tables, so VLOOKUP is not a satisfactory tool.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[joel009]

I am at the stage of adding the formula to the cell. I am using:
ActiveCell.Formula = strFormula
where strFormula is:
"=INDEX(Previous!.Range(.Cells(4, 40), .Cells(115, 40)),MATCH(GE!D4,PREVIOUS!.Range(.Cells( 4 , 4), .Cells( 115 , 4),0),1)"

I am getting a 1004 error "Application Defined or Object Defined error".

Is it because I am using a string to set a formula or am I so far off I don't know it?

Thank again for helping me out on this.

OOTO until tomorrow, they only let me stay here until 4 PM. Ain't that wonderful!!!!

Joel

 

[SkipVought]

keep your CATEGORIES straight (formula vs VBA)

strFormula = "=INDEX(Previous![b]" & .Range(.Cells(4, 40), .Cells(115, 40)).Address & "[/b],MATCH(GE!D4,PREVIOUS![b]" & .Range(.Cells( 4 , 4), .Cells( 115 , 4)).Address & "[/b],0),1)"

Any reason you are doing this in VBA and not directly on the sheet?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[joel009]

Thanks Skip - Yes I am creating a macro to accomplish this task. The entire process takes the end users about 3.5 hours a week. The macro request is about 15 months old.
I have been working in the sheet to learn, or try to learn, how the Index Match combo works. I was told to use the VLOOKUP. In order to do that the end user was copying the lookup column to column A in both sheets and then running the VLOOKUP. During development I discovered a lot of folks prefer the INDEX/Match method. So my adventure has been to figure both out and use the better IMHO. I think the INDEX/MATCH method is more flexible thus far.
I have been switchinng around trying the address or .cell. I was going to change back to the
Address' but the .cell will work if I can get the string to work.

Skip - I will give your suggestin a try and post back.

Thank you!

Joel

 

[SkipVought]

HINT:

When structuring a complex string like this, you can use faq707-4594 to observe the value in the string, along with other objects and variables.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[joel009]

Hi, I am this far:

Public Function Format_PayBill_Audit_Pivot_Report()
'Created by Joel Hardesty Apr 2013
'Creates 2 WB's with pivot tables

Dim intLastColPrev As Integer
Dim intLastColSht1 As Integer
Dim strWB_Report As String
Dim strWB_Ref As String

Dim lngLastRowPrev As Long
Dim lngSheet1_LastRow As Long


Dim rngTemp As Range
Dim rngSht1MatchCol As Range
Dim rngPreviousShtRange As Range
Dim rngPrevShtMatchCol As Range
Dim strSearchCell As String
Dim strSearchColumn As String
Dim intDestinationColOffsetfromSearchColumn As Integer

Dim strSht1Name As String
Dim strSht2Name As String
Dim strMatchcell As String
Dim strReference As String
Dim strLookup_Array As String
Dim strFormula As String
Dim intSht1CritriaColPos As Integer
Dim intSht2CritriaColPos As Integer
Dim inteARFlagPos As Integer


Dim strMatchCol As String


    strWB_Report = ActiveWorkbook.name
'open user form 1, let user select Account, capture Buyer Code and Password

    'open WB, load cboRpt from sheet
    Workbooks.Open ("\\hqntapp02\pbar\RESEARCH TEAM\Pay Bill Audit Pivot table reports\PayBill_Audit_Pivot_Ref.xlsx")
    strWB_Ref = ActiveWorkbook.name

    Load UserForm1
    
    With UserForm1
        .Caption = "Select Account"
    
        With .cboRpt
            .ColumnCount = 3
            .ColumnWidths = "1,0,0"
            .List = Workbooks(strWB_Ref).Sheets("CWO PASSWORDS").Range("A1").CurrentRegion.Value
        End With
    End With

    UserForm1.Show

'User must copy previous weeks report into current WB
'and rename to "Previous"

'Unhide all columns,Remove filters, colors from both sheets
'select Previous sheet
    Workbooks(strWB_Report).Sheets(2).Activate
    Call CleanSheet
    'get last col for Previous sheet
    intLastColPrev = ActiveSheet.Cells.Find(What:="*", _
                    SearchDirection:=xlPrevious, _
                    SearchOrder:=xlByColumns).column
    
                    
    lngLastRowPrev = ActiveSheet.Cells.Find(What:="*", _
                            SearchDirection:=xlPrevious, _
                            SearchOrder:=xlByRows).row

    intSht2CritriaColPos = WorksheetFunction.Match("CRITERIA", Sheets(2).Rows(3), 0)
    inteARFlagPos = WorksheetFunction.Match("eAR Flag", Sheets(2).Rows(3), 0)

'select first sheet
    Workbooks(strWB_Report).Sheets(1).Activate
    Call CleanSheet
    'get last col for Sheet(1)
    intLastColSht1 = ActiveSheet.Cells.Find(What:="*", _
                    SearchDirection:=xlPrevious, _
                    SearchOrder:=xlByColumns).column
                    
    lngSheet1_LastRow = ActiveSheet.Cells.Find(What:="*", _
                            SearchDirection:=xlPrevious, _
                            SearchOrder:=xlByRows).row
                            
    intSht1CritriaColPos = WorksheetFunction.Match("CRITERIA", Sheets(1).Rows(3), 0)
    
'set variables for Sheet1

    strSht1Name = Sheets(1).name
    
    'get col # for Criteria Column
    Set rngTemp = Sheets(1).Rows(3).Find("Criteria")
    
    If Not rngTemp Is Nothing Then

        intSht1CritriaColPos = rngTemp.column
        Set rngTemp = Nothing
    Else
        MsgBox "Column Criteria Not Found on Sheet: " & strSht1Name _
                & vbCrLf & vbCrLf & "Please verify the column name Criteria is in the first worksheet.", _
                 vbOKOnly, "Missing Column 'Criteria' in Worksheet " & strSht1Name & "!"
        Exit Function
    End If
                                                        
    With Sheets(strSht1Name)
                                                    'row,col                          'row,col
        strMatchcell = .Range(.Cells(4, intSht1CritriaColPos), .Cells(4, intSht1CritriaColPos)).Address
    End With
    'remove $
    strMatchcell = Replace(strMatchcell, "$", "", 1, , vbTextCompare)
    strMatchcell = strSht1Name & "!" & strMatchcell
    
'set variables for Sheet2 "Previous"
    strSht2Name = Sheets(2).name

    'get col # for Criteria Column
    Set rngTemp = Sheets(2).Rows(3).Find("Criteria")
    
    If Not rngTemp Is Nothing Then

        intSht1CritriaColPos = rngTemp.column
        Set rngTemp = Nothing
    Else
        MsgBox "Column Criteria Not Found on Sheet: " & strSht2Name _
                & vbCrLf & vbCrLf & "Please verify the column name Criteria is in the first worksheet.", _
                 vbOKOnly, "Missing Column 'Criteria' in Worksheet " & strSht2Name & "!"
        Exit Function
    End If
    
    With Sheets(strSht2Name)
                                                           'row,col                    'row,col
        strReference = .Range(.Cells(4, intLastColPrev), .Cells(lngLastRowPrev, intLastColPrev)).Address
    
        strMatchCol = .Range(.Cells(4, intSht1CritriaColPos), .Cells(lngLastRowPrev, intSht1CritriaColPos)).Address
    End With
    
    strReference = strSht2Name & "!" & strReference
    
    strMatchCol = strSht2Name & "!" & strMatchCol
    
    
'use INDEX and MATCH to add previous comments to last col in sheet 1
'  *****  Note - no need to name cell for return value, it is the cell the formula sits in
'  ***** Header row in row 3
'working formula
'=INDEX(PREVIOUS!$AN$4:$AN$115,MATCH(GE!D4,PREVIOUS!$E$4:$E$115,0),1) 'use this one
'=VLOOKUP(D4,PREVIOUS!$E$4:$AN$115,37,FALSE)

'generic formula
'=INDEX(reference, MATCH(lookup_cell, lookup_array, match_type), column_num)
'
'******   Build this string dynamically
'            'Data Range Return value           'Column to find Match
'    =INDEX(PREVIOUS!$AN$4:$AN$115,MATCH(GE!E4,PREVIOUS!$E$4:$E$115,0),1) 'use this one
'            '$ will not increment        'match cell                   'offset from Index for return value
'            'with AutoFill               'will be incremented          '1 returns same column value
'
'
'**********************


    strFormula = "=INDEX(" & strReference & ",MATCH(" & strMatchcell & "," & strMatchCol & ",0),1)"

    Sheets(1).Cells(4, (intLastColSht1 + 1)).Formula = strFormula
    
    
'    Range("AK4").AutoFill Range("AK4:AK" & lngLastRowPrev - 1)
    With Sheets(1)
        Range(.Cells(4, (intLastColSht1 + 1))).AutoFill Range(.Cells(4, (intLastColSht1 + 1), .Cells(lngSheet1_LastRow, (intLastColSht1 + 1))))

    
    End With

Thank You All for your Help

It hangs on the last line with the Error:Error 450, Wrong number of arguments or invalid property assignmnet.

Can anyone see what is wrong? I would like to to use the .cells but am having trouble.
Have a star both Skip and Brad and thanks for the hep.
PS, I have many extra variable named and not used, I will clean that up before releasing it and I still need to create a pivot table but the knowledge I gained here will help tremendously!

Joel

 

[SkipVought]

please explain what your intentions are for that last statement.

I think that what you want is...

    With Sheets(1)
        Range(.Cells(4, (intLastColSht1 + 1), .Cells(lngSheet1_LastRow, (intLastColSht1 + 1)))).AutoFill
   
    End With

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[byundt]

Joel,
Does this work?

With Sheets(1)
        .Cells(4, intLastColSht1 + 1).AutoFill .Range(.Cells(4, intLastColSht1 + 1), .Cells(lngSheet1_LastRow, intLastColSht1 + 1))

Your parentheses were causing problems.

Brad

 

[joel009]

Hi Skip - I was attempting to AutoFill the column with the formula in, the code above successfully placed it in the first cell(row 4) in the column.

Driving home I thought I had it figured out because there was no .Address reference in my line of code:

Range(.Cells(4, (intLastColSht1 + 1))).AutoFill Range(.Cells(4, (intLastColSht1 + 1), .Cells(lngSheet1_LastRow, (intLastColSht1 + 1))))

Brad hit the nail on the head and it works fine. Extra parenthesis - sheesh. I thought it would treat it like a grouping. Wow, a whole new world in Excel VBA. But according to my boss and the head programmer: "If you code based on VB 5 or 6, you will be fine'

I'm calling this one done and If I could give you both another star I would. Thanks!

Now to create a Pivot table - may be a new thread coming soon.

I had so much trouble with this subject and this was a very informative thread for me. Either my research skills or my key word searches in this forum produced very little. I used google a lot, will do so more often but this forum helps a lot!!!



Joel

 

[SkipVought]

you only need the .address property when you are structuring a formula to go on a sheet. This most recent code is just autofilling a value.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[byundt]

Joel,
Your problem wasn't having too many parentheses, but rather not using them to close out object references correctly. For example, in the second part of your AutoFill statement, notice how your first .Cells reference isn't closed out until after the second .Cells reference:
Range(.Cells(4, (intLastColSht1 + 1), .Cells(lngSheet1_LastRow, (intLastColSht1 + 1)))) 'What you had
Range(.Cells(4, (intLastColSht1 + 1)), .Cells(lngSheet1_LastRow, (intLastColSht1 + 1))) 'This works. One right parenthesis moved from second .Cells to first.
Range(.Cells(4, intLastColSht1 + 1), .Cells(lngSheet1_LastRow, intLastColSht1 + 1)) 'Extra parentheses removed to reduce confusion

Cells(row reference, column reference) returns a Range object pointing to a particular cell
Range(Range1, Range2) produces a Range object pointing to the rectangular block of cells with Range1 and Range2 at upper left and lower right corners

Your problem in the original statement was trying to pass the first Cells three parameters: a row index of 4, a column index of intLastColSht1+1, and an unexpected third parameter of the value returned by .Cells(lngSheet1_LastRow, intLastColSht1+1). It is this unexpected third parameter that resulted in the error message: Wrong number of arguments.

I initially summarized all this as "your parentheses were causing problems" but probably should have gone into a little more detail.

Brad