# 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 &amp; 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 &amp; match, and return a row of data

Attach a workbook...

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

Very sorry I had copied details from a word doc and it didn't come out as I intended so I upload a workbook now and hope it's clearer and I would like a formula please. Regards

## Files

• Re: Index &amp; 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 &amp; 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 &amp; match, and return a row of data

Perhaps something like this? using array formulas...

cheers

## Files

• Re: Index &amp; 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!