Help with LookUp, IF etc

[Zillon]

Hi

I'm trying to automatically create the result column as a text field. I need to look up a range of values in the Group field, find all occurrences of a matching number (e.g. 1 or 2 - although there could be more) and then create the results field from adjacent cells. I expect there is a way using nested IF and LOOKUP functions but I just can't work out how to do it. Please see what I'm trying to achieve below. I'd really appreciate some help with this.


Group Code Description Result
c955 Product Z c955 Product Z
1 d951 Product A D951/D952/D953 Product A
1 d952 Product B
1 d953 Product D
d959 Product E d959 Product E
2 D444 Product F D444/D555 Product F
2 D555 Product G

[alansidman]

Look at this recent thread where the OP was looking to do something similar.

http://www.excelforum.com/excel-form...cate-data.html

[Zillon]

Hi Alan

Thanks for the speedy response. Whilst I struggled to understand how the formula works I don't think it does what I need it to. I'm trying to create a text string from multiple fields but only when they need to be grouped (hence why I thought I could work with a group identifier)into that description. Sadly I don't have any experience with Macros either. I'm a bit stumped with this.

[ChemistB]

I cannot sort out what is a Group Code, Description and Result from your post. Can you upload it as an Excel sheet? (Go Advanced> Manage Attachments)

[Zillon]

Hi

Does this do the trick?

[How How]

Sorry, wrong post!

[tigeravatar]

Zillon,

Welcome to the forum!
Unfortunately, Excel's native CONCATENATE() function doesn't work with arrays. This means that there is no way Excel can perform what you're asking for without the use of VBA or an add-in.
I have created a UDF for tasks like this and it is located here:
http://www.excelforum.com/tips-and-t...geravatar.html

Attached is a modified version of your posted workbook.
It contains the ConcatAll UDF that I created. Then in cell D5 and copied down is this array formula:

Please Login or Register  to view this content.

Note that array formulas must be entered with Ctrl+Shift+Enter and not just Enter. That's how the formula gets surrounded by the curly braces {}. Do not try to add those yourself.