INDEX/MATCH Formula - beyond me

    Hi Folks


    I'm trying (really hard), to create a formula that indexes an area then carries out multiple matches in order to return a value onto a cell on another sheet.


    I've got as far as: =IFERROR(INDEX(Sheet1!E5:BM20,MATCH(Sheet2!C5,Sheet1!C5:C20,0),MATCH(Sheet2!D4,Sheet1!D5:D20,0),MATCH(Sheet2!C3,Sheet1!E3:BM3,0)),0) but it's just not working.


    I've attached a sample sheet to show what it is I'm try desperately to do but right now I'm losing the will to live. Any help would be so very much appreciated please??


    Kind regards and thank you..... :)


    DezB


    Match-Index - Copy.xlsx

    Hi DezB,


    Hope you are doing fine :)


    You have designed a basic structure ... almost dedicated to creating problems ;(


    Take Sheet1 ... Why don't you have (at least) the same number of columns allocated to each month ?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Like 1

    Hi Carim


    It's good to hear from you after all this time, I hope all is well?


    Ha, yes, I quite agree, unfortunately the layout in sheet one is defined elsewhere in the group and I have no control over it. I wish I did, it's horrible....


    That's what it causing me so many headaches... :(


    Kind regards


    DezB

    To go one step further ...


    Say in Sheet2, you need to locate the row in Sheet1, you would need the following Array Formula

    Code
    =MATCH(B6&D4,Sheet1!$B$1:$B$20&Sheet1!$C$1:$C$20,0)

    and still in Sheet2, to locate the column K in Sheet1, you would need

    Code
    =MATCH(C3,Sheet1!$3:$3,0)+7

    As you can it, the Offset +7 to locate "Core function Actual" column is totally dependent on the basic structure you have designed.


    So if all months in Sheet1 do not have the same number of columns ... you are heading for troubles ... :(

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Like 1

    Hi Carim.


    That's a good point, but if I replicate sheet 1 onto sheet to ensure I have the same number of columns the formula should work.


    Kind regards


    DezB

    Can you add any formulas to Sheet1?

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    In that case, you could do some thing like this:


    In d2 on sheet 1 enter:

    =IF(TRIM(D4)="Core function actual",COLUMN(),"")


    and copy across to the end of your data.


    Then on sheet 2 in C5 enter:


    =IFERROR(LOOKUP(2,1/(Sheet1!$B$5:$B$20=$B5)/(Sheet1!$C$5:$C$20=C$4),INDEX(Sheet1!$5:$20,0,MINIFS(Sheet1!$2:$2,Sheet1!$2:$2,">"&MATCH($C$3,Sheet1!$3:$3,0)))),0)


    and fill across and down for the 6 levels. You can then copy and paste that across to the next 6 and replace $C$3 with $I$3 and repeat for each block of 6. Alternatively, you could enter the date in each cell in row 3 on sheet 2 (you can hide any you don't want visible using number formatting) and then just amend the $C$3 in the formula to C$3 and you can then simply fill across for all the data.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    Like 1

    Hi Rory.


    Thank you, I shall have a go. Much appreciation to both you and Carim. I knew someone would be able to help here. :)


    Kind regards


    DezB

    Can I just say.... I have a huge grin on my face right now, this all has made my day.... Thank you both so very much... :)

    Cheers


    DezB

    Glad we could help, and thanks for the kind words - they have made my day! :)

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    rory


    Well Done !!! ... Excellent Idea :)


    Smart workaround an inconsistent sheet structure :thumbup:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

Participate now!

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

Register Yourself Login