Results 1 to 7 of 7

Thread: MATCH & INDEX (what am I missing)

  1. #1

    MATCH & INDEX (what am I missing)



    Register for a FREE account, and/
    or Log in to avoid these ads!

    This is going to be complicated to explain but I'll try my best. I've attached my project to help you understand.


    I have three sheets TAKEOFF, PCFB & PCFB1.

    In cell G6, I have this formula: =INDEX(PCFG!$U$6:$AA$37,MATCH(C6,PCFG!$U$6:$U$37,0),MATCH(D6,PCFG!$U$6:$AA$6,0)) in cell G6 of the TAKEOFF sheet.

    I'm using this formula to return the intersecting cell on the PCFG sheet base on what I have in cell C6 & D6 of the TAKEOFF sheet.

    In its current state, I have "PCFG" (a sheet name) baked into the formula. I need that part to be dynamic, based on which sheet name I put in cell B6.

    The formula currently doesn't use cell B6, but I need to be able to specify a sheet name "PCFG" or "PCFG1" in B6 and have the rest of the formula pull the data from that sheet.

    Can this be done?
    Attached Files Attached Files

  2. #2
    Wizard Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,615
    Articles
    0
    Excel Version
    2010 on Xubuntu
    This could be done by using the INDIRECT function.
    But this will make formulas cumbersome and eventually slow calculations down as INDIRECT is a volatile function

    As the only difference between PCFG sheets is the discount percentage why not use L6:R37 form PCFG sheet as base prize and enter the discount in B6 ?
    Adapt your formula with the necessary ranges and multiply it by B6

  3. #3
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    869
    Articles
    0
    Excel Version
    Excel 2013
    Quote Originally Posted by airpuncher View Post
    Can this be done?
    See if you get this formula with the INDIRECT function solves the problem, such as a colleague @Pecoflyer suggested
    Put this formula in G6
    Code:
    =INDEX(INDIRECT("'"&$B6&"'!"&CHAR(COLUMN(CG85))&ROW($U$6)&":$AA$37"),MATCH(C6,INDIRECT("'"&$B6&"'!"&CHAR(COLUMN(CG85))&ROW($U$6)&":$U$37"),0),MATCH(D6,INDIRECT("'"&$B6&"'!"&CHAR(COLUMN(CG85))&ROW($U$6)&":$AA$6"),0))
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

  4. #4
    Wizard Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,615
    Articles
    0
    Excel Version
    2010 on Xubuntu
    @navic
    Thanks for quoting me, but I think the use of INDIRECT is not the most efficient technique in this case

  5. #5
    Quote Originally Posted by Pecoflyer View Post
    This could be done by using the INDIRECT function.
    But this will make formulas cumbersome and eventually slow calculations down as INDIRECT is a volatile function

    As the only difference between PCFG sheets is the discount percentage why not use L6:R37 form PCFG sheet as base prize and enter the discount in B6 ?
    Adapt your formula with the necessary ranges and multiply it by B6



    Thanks, Pecoflyer. You're right, in the example, the percentage is the only difference between the PCFG sheets. This won't be the case once I flesh this thing out. Thanks for the suggestion though.

    What do you mean by "INDIRECT is a volatile function"?

  6. #6

  7. #7
    Wizard Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,615
    Articles
    0
    Excel Version
    2010 on Xubuntu
    What do you mean by "INDIRECT is a volatile function"?
    http://www.decisionmodels.com/calcsecretsi.htm

Tags for this Thread

Posting Permissions

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