Results 1 to 3 of 3

Using INDEX MATCH in VBA with Variable Lookup Locations

Threaded View

  1. #1
    Registered User
    Join Date
    05-06-2019
    Location
    Washington, D.C.
    MS-Off Ver
    2016
    Posts
    1

    Using INDEX MATCH in VBA with Variable Lookup Locations

    I am having trouble using variables within the lookup criteria in Index Match. Some background: I use the following code to set a variable's value to the row # of whatever cell contains "Current" within column B:

    Dim rowHeaderNum As Integer
    
        rowHeaderNum = 0
        rowHeaderNum = Application.Match("Current", ActiveSheet.Range("B:B"), 0)

    Then I use the below to store the column # of the cell within the row 'rowHeaderNum' that contains the value "CurrentActual" to another variable:

    Dim currActColNum As Integer
    
    currActColNum = 0
        currActColNum = Application.Match("CurrentActual", Rows(rowHeaderNum & ":" & rowHeaderNum), 0)
    Below is the Index Match line that I can't get to work:

    Dim currActRev As Double
    
        currActRev = 0
        currActRev = Application.Index(Columns(currActColNum), Application.Match("Gross Operating Profit", Columns("N:N"), 0))
    currActRev will store a dollar amount. The Match function will always use column N as the lookup array. When I run the Index Match line I get a type mismatch error in the debugger.

    Thanks in advance!
    Last edited by jgodoyle2; 05-06-2019 at 02:08 PM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Index and Match To Extract All Unique Locations Meeting Reoccurring Criteria Value
    By rlh06 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-05-2015, 11:25 AM
  2. index/match formula to split stock locations
    By interested in forum Excel General
    Replies: 2
    Last Post: 05-08-2014, 07:01 AM
  3. Replies: 6
    Last Post: 04-30-2014, 02:42 AM
  4. [SOLVED] Problem with the getting all locations sales using iferror, index, match
    By brandedadnan in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-10-2013, 03:14 AM
  5. Dynamic or Variable Lookup array in INDEX, MATCH or VLOOKUP
    By kishor_c in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-16-2012, 09:46 AM
  6. Replies: 2
    Last Post: 11-05-2011, 03:26 PM
  7. Three variable lookup/index/match whatever
    By manalex in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-21-2005, 11:05 PM

Tags for this Thread

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