+ Reply to Thread
Results 1 to 6 of 6

Help with Index, indirect, and match

  1. #1
    Registered User
    Join Date
    07-08-2014
    Location
    United States
    MS-Off Ver
    2013
    Posts
    2

    Help with Index, indirect, and match

    I am doing a project for work, and I am dealing with a large file with multiple tabs. I am building a table for a graph that is going to feed off of two data validation lists (B4 and T4). The graph/table is going to pull different information based off B4, which is either YTD or FY, and T4 which is multiple Regions. So I am using Indirect to reference my 2 tabs: SIMPLE_YTD, or SIMPLE_FY. However, I keep getting a #REF! error in my indirect formulas.


    Here is the formula I am using: =INDEX(INDIRECT("SIMPLE_"&$B$4),MATCH(VLOOKUP($S$4,$AE$3:$AF$23,2,FALSE),INDIRECT("SIMPLE_"&$B$4),0),MATCH(R16,INDIRECT("SIMPLE_"&$B$4),0))

    Am I using the indirect formula Wrong?

    Any help would be greatly appreciated, thanks.

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Help with Index, indirect, and match

    Hi,

    If you're using sheet tab names you'll need to incorporate the sheet defining character "!" e.g

    INDIRECT("'SIMPLE_"&$B$4&"'!")...

    but since you're using an INDEX then you also need to be including a Range reference in the first element of the Index which I don't see.

    Probably best if you upload the workbook.
    Last edited by Richard Buttrey; 07-08-2014 at 06:10 PM.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,926

    Re: Help with Index, indirect, and match

    Hi and welcome to the forum

    Hard to say for sure without seeing a sample of what you are working with, but do you, sor instance, have a sheet named Simple_YTD or Simple_FY?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,984

    Re: Help with Index, indirect, and match

    It looks to me like there's something wrong with the formula's syntax (easier if you'd posted a sheet).

    Index(range 1,(match(cell,range2,qualifier),match(cell2,range3,qualifier)).. Are B4 and T4 ranges, or cell references? If ranges, you shouldn't need the sheet name. Can you safely call a range something that could also be a cell reference? Can you post an example sheet?
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  5. #5
    Registered User
    Join Date
    07-08-2014
    Location
    United States
    MS-Off Ver
    2013
    Posts
    2

    Re: Help with Index, indirect, and match

    Thanks for the prompt response, unfortunately I am dealing with some rather confidential information and can't post the workbook or sheets without getting sued. That being said, Simple_YTD and SIMPLE_FY are both sheets. I am pulling info from these sheets into my table, and I am dealing with lists in both B4 and T4.

    Do I have to specify a range (in the indirect formula) within the sheets to tell the formula where to pull the information from?

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,984

    Re: Help with Index, indirect, and match

    Try altering to read something like =INDIRECT("'Simple_'!"&etc. However, as both Richard & I said.. the first index should be followed by a RANGE, from which you want the return to come, not a cell reference.. can you anaonymise a small portion of your sheet, & post it?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Index? Indirect? Match?
    By CpnVenice in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-13-2013, 02:43 PM
  2. Problem with INDEX, INDIRECT, MATCH, MATCH
    By JO505 in forum Excel General
    Replies: 5
    Last Post: 09-01-2011, 05:51 PM
  3. Index/Indirect/Match Help
    By robertsclark in forum Excel General
    Replies: 2
    Last Post: 04-20-2011, 10:24 AM
  4. Using INDEX/MATCH with indirect
    By jdpjtp910 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-15-2010, 11:39 AM
  5. Index, Indirect, Match
    By ruslan_adx in forum Excel General
    Replies: 1
    Last Post: 10-08-2009, 03:35 AM

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