Get Header data and row/column data value

    hello,

    I was trying to get in column H concatenated Header data and (next to the right) column data for maximum value of Percentage value in a row which is calculated in column G...


    tried some formula with ''INDEX,MATCH'', but must admit my skills in excel are still at baby level... :)


    for better understanding, I made a screenshot of my table where I've manually filled ''data'' as it should look as a result in column H


    Hope anyone could help

    best regards

    1. I have no idea what you are asking. Your explanation is confusing and not clear.

    2. We cannot manipulate data in a picture

    3. Suggest you upload a sample file.

    4. Using the sample file data you upload, also mock up and upload a solution of what you want your result to look like.

    5. You may wish to explain once again the logic you use in the samples you upload

    Hi, AlanSidman... I am sorry that I was not clear in my explanation,..


    I'll try to explain, again, what I am asking as a result in column H... here I have calculated in Column G, maximum value from Columns A, C and E... Used formula =LARGE((A2,C2,E2),1)


    in column H, if maximum value is in the cell A2, my result should have Column B Header value and cell B2 value... if max percentage value is from cell C2, then the result should show Column D Header and cell D2 value...


    I have manually filled data in Column H as an example which values i want as result

    Try.............


    1] If you have Office 365 or Excel 2019 CONCAT function, use this in H2 and copied down :


    =""""&CONCAT(INDEX($A$1:$F2,N(IF(1,IF({1,0},1,ROW(A2)))),MATCH(G2,A2:F2,0)+1)&" ")&""""


    Or,


    2] If you don't have CONCAT function, use this in H2 and copied down :


    =""""&OFFSET($A2,ROW(A$1)-ROW(A2),MATCH(G2,A2:F2,0))&" "&OFFSET($A2,0,MATCH(G2,A2:F2,0))&""""


Participate now!

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

Register Yourself Login