# Help with concatenate

#### Sudarshan_1026

##### Board Regular
Hi All,

Need you help in concatenate function

i need to concatenate values of a row based on the column header.i cannot use a simple concatenate as the data will be pasted by different individuals and column numbers might change. i am not getting through any logic to do it using the match function as well. the data will be pasted in random columns every time someone inputs it.

Sudi

### Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
What are the column header names?
How many concat will there be?
Also can you post some sample data to work with?

Thanks

Colin

What are the column header names?
How many concat will there be?
Also can you post some sample data to work with?

Thanks

Colin

There will be three concat.. date, home, and away are the headers. it will have another 10 headers in the page

Sudi

Assuming your headers are from column A-J and are spelt "Date", "Home", "Away" this should do the trick

=CONCATENATE(TEXT(INDEX(A2:J2,MATCH("Date",\$A\$1:\$J\$1,0)),"dd/mm/yyyy")," - ",INDEX(A2:J2,MATCH("Home",\$A\$1:\$J\$1,0))," - ",INDEX(A2:J2,MATCH("Away",\$A\$1:\$J\$1,0)))

Assuming your headers are from column A-J and are spelt "Date", "Home", "Away" this should do the trick

=CONCATENATE(TEXT(INDEX(A2:J2,MATCH("Date",\$A\$1:\$J\$1,0)),"dd/mm/yyyy")," - ",INDEX(A2:J2,MATCH("Home",\$A\$1:\$J\$1,0))," - ",INDEX(A2:J2,MATCH("Away",\$A\$1:\$J\$1,0)))

Excellent, it worked perfect for me.. thanks a ton, made my day.Index was the trick which i was missing..

You are very welcome, thanks for providing feedback

You are very welcome, thanks for providing feedback

Would you be able to help me with formula through which i can look for a specific word in the cell and return a value of my choice..

Ex A1 cell has Operator Module - branding - 10% .. if teh cell A1 contains branding it should return value 1

Sudarshan

Will branding be the only criteria?

Will branding be the only criteria?
I somehow managed to get it going using ISNUMBER(FIND(

i am stuck at a different problem.

I am using a IF statement and doing a vlookup to pull the value from another sheet and further trying to use the OR function to see if the lookup value is equal to any one of the OR values its throwing an error.

Can you help me on this..

Sudi

Replies
3
Views
422
Replies
6
Views
353
Replies
1
Views
387
Replies
3
Views
256
Replies
4
Views
432

1,203,757
Messages
6,057,164
Members
444,909
Latest member
Shambles111

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back