Results 1 to 8 of 8

INDEX MATCH to return the row number?

Threaded View

  1. #1
    Registered User
    Join Date
    10-08-2012
    Location
    Cedar Falls, Iowa
    MS-Off Ver
    Excel 2007
    Posts
    50

    INDEX MATCH to return the row number?

    I am doing a multiple criteria search using INDEX MATCH. The code works well to produce a formula, but what I really need it to do is return the row number when it finds the multiple match.

    Sub new_forecast()
        Set fSheet = Workbooks("TEST 2.xlsx").Worksheets("Product Split Filter")
        Set nSheet = Workbooks("TEST.xlsm").Worksheets("2021 Forecast")
        
        fSheet.Activate
        fLastRow = ActiveSheet.Cells(65000, 1).End(xlUp).row
        
        Rows("1:1").Select
        With Selection
            .Find(What:="fModel").Activate
            fModCol = ActiveCell.Column
            .Find(What:="Factory").Activate
            fFacCol = ActiveCell.Column
            .Find(What:="Sub_Region").Activate
            fRegCol = ActiveCell.Column
            .Find(What:="Units").Activate
            fUnitCol = ActiveCell.Column
        End With
        
        nSheet.Activate
        nLastRow = ActiveSheet.Cells(65000, 1).End(xlUp).row
        
        Rows("1:1").Select
        With Selection
            .Find(What:="fModel").Activate
            nModCol = ActiveCell.Column
            .Find(What:="Factory").Activate
            nFacCol = ActiveCell.Column
            .Find(What:="Sub_Region").Activate
            nRegCol = ActiveCell.Column
        End With
        
        fSheet.Activate
        
        For fCast = 2 To fLastRow
            fModel = fSheet.Cells(fCast, fModCol).Value
            fFactory = fSheet.Cells(fCast, fFacCol).Value
            fRegion = fSheet.Cells(fCast, fRegCol).Value
            fUnits = fSheet.Cells(fCast, fUnitCol).Value
                nSheet.Activate
                 Range(Cells(14 + fCast, 1), Cells(14 + fCast, 1)).FormulaArray = "=INDEX(T2:T10, MATCH(" & Chr(34) & fModel & fFactory & fRegion & Chr(34) & ", M2:M10 & N2:N10 & R2:R10, 0))"     ' I really need this to just return the row number
        
    Next fCast
        
    End Sub
    Last edited by cabroncito29; 09-30-2015 at 02:24 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 5
    Last Post: 10-16-2016, 02:33 AM
  2. [SOLVED] Vlookup/index/match to return all values that match
    By Asil01 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-09-2014, 12:49 PM
  3. [SOLVED] Combining 3 Formulas: Return all Names that Match Criteria Using Index/Match
    By bchilme in forum Excel Formulas & Functions
    Replies: 20
    Last Post: 09-29-2014, 09:28 AM
  4. Using Index/Match to return multiple values for one match
    By superboy in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-10-2014, 06:21 PM
  5. VLOOKUP/INDEX/MATCH to return all values that match
    By lijia00 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-05-2014, 11:56 AM
  6. [SOLVED] Lookup/Index/Match to Return Value of Higher number rather than lower# when inbetween
    By ReedDOT in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-16-2013, 11:56 PM
  7. Replies: 3
    Last Post: 05-08-2013, 02:10 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1