+ Reply to Thread
Results 1 to 3 of 3

Named range lookup using Index/Indirect/Match Function calls in Macro

  1. #1
    Registered User
    Join Date
    02-10-2013
    Location
    Austin
    MS-Off Ver
    Excel 2010
    Posts
    3

    Named range lookup using Index/Indirect/Match Function calls in Macro

    Hi
    I have a cell which has the following formula.
    (INDEX(INDIRECT(W16),MATCH(B$3&B$4&"C",INDIRECT(X16),0),10)
    It uses an Indirect function to know which sheet to lookup. I use named list (range) to define the index lookup array. The named range variable in the above formula is in cell W16. X16 is yet another named range variable. Match function returns the first element of the index array variable and the second array variable is a constant. In this scenario 10.

    Now I want this whole thing to be called in a macro function. I would like to update the cell value with the above formula (instead of entering it directly in the cell)
    e.g. Cells(2,2).value = (INDEX(INDIRECT(W16),MATCH(B$3&B$4&"C",INDIRECT(X16),0),10)

    This is somewhat similar to this question http://www.excelforum.com/excel-form...and-match.html
    but I would like to be able to call this in a macro function.

    Any help would be appreciated.

    Thanks a lot
    Last edited by sgopan; 02-27-2013 at 05:19 PM.

  2. #2
    Valued Forum Contributor Hawkeye16's Avatar
    Join Date
    02-27-2013
    Location
    Holland
    MS-Off Ver
    ├┤ Pew Pew
    Posts
    441

    Re: Named range lookup using Index/Indirect/Match Function calls in Macro

    I would try

    Please Login or Register  to view this content.
    note the double quotes within the formula. You could also just use an apostrophe instead of double quotes if you wanted.
    Last edited by Hawkeye16; 02-27-2013 at 06:04 PM.

  3. #3
    Registered User
    Join Date
    02-10-2013
    Location
    Austin
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Named range lookup using Index/Indirect/Match Function calls in Macro

    Hi Hawkeye,
    Thanks a lot, this simple trick works great.
    Thanks

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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