[Solved] Index Match with multiple matches

[excler]

Hello,

I have a database with about 5000 entries. The entries are classified by industries, but always in an extra row. Now I want to combine all this into one row.

I tried it with Index - Match but it always takes the first value (if it is empty, index match takes the empty value).

Can you help me?

 

[Bishawajit]

Hello,

I have a database with about 5000 entries. The entries are classified by industries, but always in an extra row. Now I want to combine all this into one row.

I tried it with Index - Match but it always takes the first value (if it is empty, index match takes the empty value).

Can you help me?

Dear excler, Thank you for your question. Please have a look at the solution below.
The following steps can be used to combine the industries for each entry into one row using the INDEX-MATCH function:

• Next to the industry column, which we'll suppose is column B, add a new column.
• Use the IF function to determine whether the cell in column B is not blank in the new column, which we'll suppose is column C. Return the industry if it is not blank. Return a blank string if it is empty. For instance, insert the formula =IF(B2>"", B2, "") in cell C2 and drag it down to encompass all of the values in column B.

• Use the INDEX-MATCH function to find the industries associated with each entry and combine them into a row in a different cell (let's say cell E2). For example, the formula in cell E2 can be =INDEX($C:$C, MATCH($A2, $A:$A, 0))&", "&INDEX($C:$C, MATCH($A2, $A:$A, 0)+1)&", "&INDEX($C:$C, MATCH($A2, $A:$A, 0)+2) assuming the company names are in column A and there are three industries for each business. The industries for each firm are concatenated in this formula after being separated by commas and spaces. The formula can be modified depending on how many industries each company operates in.
• To include all of the values in column A, drag the formula in cell E2 down.

I hope you will get your solution.