Drag down formula and change column names in order

  • Hello OzGrid Members,


    I'd like to drag down my formula while keeping everything as it is except the indexed column name (first part of the formula). Everything with desired results is in the attached file.


    Thank you in advance.

  • Hi,


    Perhaps try this Array* formula (but please fix G2 & H2 to match the corresponding table exactly.. i.e. need a space between text and number).


    =IFERROR(INDEX(Sheet1!$E$2:$G$12, MATCH($G$2&$H$2, Sheet1!$C$2:$C$12&Sheet1!$D$2:$D$12,0),ROWS($G$5:$G5)),"")


    copied down


    [arf]*[/arf]

    Where there is a will there are many ways. Finding one that works for you is the challenge!


  • Hello NBVC thank you very much for your unique solution as always.


    Just a little question: I got blank results. I guess it's related to something you said, but really couldn't identify the issue here. Where should I put a space in the sheet? Or where should I change so that formula works as expected?

    [SIZE=4]"I don’t pretend we have all the answers. But the questions are certainly worth thinking about."
    Sir Arthur C. Clarke
    [/SIZE]

  • Did you enter it correctly as an array forumul? CTRL+SHIFT+ENTER, not just ENTER.

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

  • Did you enter it correctly as an array forumul? CTRL+SHIFT+ENTER, not just ENTER.


    Hello AliGW,


    Yes, already done it as you offered.

    [SIZE=4]"I don’t pretend we have all the answers. But the questions are certainly worth thinking about."
    Sir Arthur C. Clarke
    [/SIZE]

  • NBVC's formula works for me, did you do as he said and, on Sheet2 cells G2 and H2, change Group1 and Budget1 to Group 1 and Budget 1, the values in those 2 cells must EXACTLY match the column headers in Sheet1.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • NBVC's formula works for me, did you do as he said and, on Sheet2 cells G2 and H2, change Group1 and Budget1 to Group 1 and Budget 1, the values in those 2 cells must EXACTLY match the column headers in Sheet1.


    Yes! KjBox, you all are right. My mistake, sorry all. Still can't believe how i am lucky to find all these best people here. Thank you NBVC, AliGW, and Kj!

    [SIZE=4]"I don’t pretend we have all the answers. But the questions are certainly worth thinking about."
    Sir Arthur C. Clarke
    [/SIZE]


  • NBVC, just really wondering and want to know of it: How does this formula work? I mean, Row names do not change, then how does the formula get the next indexed column while everything is constant? Could you explain it for me? Thank you.

    [SIZE=4]"I don’t pretend we have all the answers. But the questions are certainly worth thinking about."
    Sir Arthur C. Clarke
    [/SIZE]

  • Hi,


    The syntax for the INDEX() function is INDEX(array,row_num,[column_num])


    The array can be one-dimensional (i.e. one column) or two-dimensional (i.e. multiple columns).
    The row_num indicates which row number within the array you want to extract from
    The column_num is required when the array is two-dimensional and indicates which column number to extract from. It is not required if array is one-dimensional)


    So in my formula the array is two-dimensional (Sheet1!E2:G12), the row_num is found using the MATCH() function as you had done. We need to now indicate which columns within the array you want to pull out (we know that it would be column numbers 1, 2 and 3 for the 3 adjacent items you want to pull). Since we are dragging the formula down, in order to extract consecutive column numbers we can use the ROWS() function. ROWS($G$5:$G5) will result in 1, which means column 1, as you drag down it changes to ROWS($G$5:$G6) which results in 2, then ROWS($G$5:$G7) results in 3...


    so the only thing that changes as you drag the formula down is the 3rd parameter of the INDEX function to extract the correct, consecutive column number.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Very very informative. Truth, i didn't get the point about ROW and what's amazing that two dimensional index has lighten up my mind now! I will build up new formulas. Thank you so much NBVC.

    [SIZE=4]"I don’t pretend we have all the answers. But the questions are certainly worth thinking about."
    Sir Arthur C. Clarke
    [/SIZE]

  • Here are a couple of sites to show you the INDEX function and it's capabilities especially in combination with the MATCH() function to find intersects.


    http://www.ozgrid.com/Excel/index-match.htm


    https://exceljet.net/excel-functions...index-function


    http://www.contextures.com/xlFunctions03.html

    Where there is a will there are many ways. Finding one that works for you is the challenge!


  • I'm following up just right now. Thank you much

    [SIZE=4]"I don’t pretend we have all the answers. But the questions are certainly worth thinking about."
    Sir Arthur C. Clarke
    [/SIZE]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!