Index & match, and return a row of data

  • I am looking for help to Index & Match and return a range as this example:


    A B C D E F G



    12 1 GER - FRA - - SWE - NOR -
    13 2 JPN - AUS SCO - USA - - BEL - SUI -
    14 3 - - - KOR - WAL - -



    I wish to match say the number 2 (A13) or any number in col A and all the data in line 13 or all the data opposite whatever number is chosen and it would be copied over as say GER - FRA (with the "dash") and all the data in that row and if possible have a comma "," between the double names or a "space" or even two spaces. .


    If the row is blank I would like the cell to be "" .


    I do hope you can follow my request and many thanks for looking.

  • Re: Index & match, and return a row of data


    Hi


    How are you wanting this to be done, by cell formula or VBA?


    I assume the data is in separate cells i.e. A13 = 2, B13 = JPN, C13 = -, D13 = AUS, E13 = SCO, is this correct? if so, are all lines the same length as they don't appear so in the above example? If there are lines using varying numbers of cells to contain data, what's the last column of the longest line?

  • Re: Index & match, and return a row of data


    Hi there,


    I'm not sure this is exactly what you want, but it should be a start. I would put the following formula


    =VLOOKUP($A$11,$A$12:$F$14,COLUMN(B11),FALSE)


    in the row above your data (B11:F11) which will populate the data based on the value put into A11, otherwise I expect the formula will get too long & contain too many lookups which will be hard on the processor.


    You can use the following formula in cell B10 to show the values you want amended to include commas etc.


    =CONCATENATE(IF(ISERROR(FIND(" -",B11,1)),B11,TRIM(REPLACE(B11,FIND(" -",B11,1),2,","))),", ",IF(ISERROR(FIND(" -",C11,1)),C11,TRIM(REPLACE(C11,FIND(" -",C11,1),2,","))),", ",IF(ISERROR(FIND(" -",D11,1)),D11,TRIM(REPLACE(D11,FIND(" -",D11,1),2,","))),", ",IF(ISERROR(FIND(" -",E11,1)),E11,TRIM(REPLACE(E11,FIND(" -",E11,1),2,","))),", ",IF(ISERROR(FIND(" -",F11,1)),F11,TRIM(REPLACE(F11,FIND(" -",F11,1),2,","))))


    There's probably a more elegant solution but let me know if this is what you want.

  • Re: Index & match, and return a row of data


    Many thanks for your all your work but I think I will have to re think my problem as I now wonder if I am asking the correct question.


    So let me have another think. Regards

  • Re: Index & match, and return a row of data


    Many thanks and apologies for not explaining it properly in the first place that's more like it.

Participate now!

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