Conditional & Contains & Array

Dear members, i wonder if you may be able to assist:

I am trying to have Excel identify contents of a target cell in same row, and then output a desired result.

simple formula can handle this for a single target: "750W"

=IF(ISNUMBER(FIND("750W",P2)), "750W", "FALSE")


but what about:

1) a more complex target, which may contain multiple components? (eg "750W"+"0.50"+"DC")
2) whereby I want Excel to automatically string together the output it finds in the target cell:

=IF(ISNUMBER(FIND("750W"AND/OR"0.50"AND/OR"DC",P2)), "CONCATENATE & INSERT VALUES FOUND", "FALSE")

I'm guessing i need to create a list (array?) somewhere for Excel to look up a range of targets, but I have no idea how to concatenate the output results into a single cell

any help gratefully received

Fantatsic - thank you very much Feronen

FDibbins, thank you for kind advice, I am very new to this forum and didn't see all the consequences of my taciturnity :P. I will gladly adjust to this rule - it benefits us all

DominicAtelier - When I return home, I will look for solution for you immediately, with proper explanation :D

@FDibbins Thanks for the advice on how to get the most out of the forum - much appreciated

@Feronen I have uploaded a simplified example xlsx as suggested

there are 3 tabs:

SEARCH: here I have manually input my desired result (consider i have 5000+ rows of data to manipulate, this example shows only 4)

MATCH: where the data is being pulled from

COMBINATIONS: all combinations & matches required for reference

Any help will be extremely gratefully received

Dominic

Okay, so here is what I managed to create:

In your case, you cannot use VLOOKUP for searching combinations of certain values (for example "d" as a color index), because it is a part of a larger text in a cell. VLOOKUP can't find exact text in bigger text string.
Solution? You have to extract text that you are interested in to another, sigle cells. For that, you have to use function for text extracion - MID:

Please Login or Register  to view this content.

You start in the SEARCH sheet. The problem is, the position of text we want to extract is variant, so we have to make formula more inteligent, using functions like FIND, LEN and SUBSTITUTE.
First I had to replace some of "," signs in your entered text with some unique symbols. For that, I used SUBSTITUTE function to replace "," in the middle of text to "*" and "&" symbols in the column C ("Helper"). Why I did that?
The explanation is the next step. For example colour symbol - it helps a lot, that your text has some pattern: before that information, there is always text "Colour". That gives me a start point to use in FIND function.
But to find the end of a text I want to extract, symbols "," wouldn't be enough - there is too much of them. That is why I replaced some of them in a first place.
Thanks to that, FIND function doesn't have a problem with finding it. So I extracted needed informations into single cells in colums D:G (Carats wasn't necessary).
Final step - after an extraction of every information I need, I concantenated them into single string in column H and then used VLOOKUP in SEARCH sheet to look for values in MATCH sheet using combinations from COMBINATIONS sheet (I created that combination in colums REF_COMBINED).

Hope, that solution Workbook will clarify what I said and hope that is what you needed

You can also build whole formula in just one column, but displaying it in separate colums is more practical IMO, because you can use it later for another searches. In single column it would look like that:

Please Login or Register  to view this content.

Hi Feronen, any chance you can have a look at this or shall I post a new thread in forum?

You missed my reply, check up

Originally Posted by Feronen

Okay, so here is what I managed to create:

In your case, you cannot use VLOOKUP for searching combinations of certain values (for example "d" as a color index), because it is a part of a larger text in a cell. VLOOKUP can't find exact text in bigger text string.
Solution? You have to extract text that you are interested in to another, sigle cells. For that, you have to use function for text extracion - MID:

Please Login or Register  to view this content.

You start in the SEARCH sheet. The problem is, the position of text we want to extract is variant, so we have to make formula more inteligent, using functions like FIND, LEN and SUBSTITUTE.
First I had to replace some of "," signs in your entered text with some unique symbols. For that, I used SUBSTITUTE function to replace "," in the middle of text to "*" and "&" symbols in the column C ("Helper"). Why I did that?
The explanation is the next step. For example colour symbol - it helps a lot, that your text has some pattern: before that information, there is always text "Colour". That gives me a start point to use in FIND function.
But to find the end of a text I want to extract, symbols "," wouldn't be enough - there is too much of them. That is why I replaced some of them in a first place.
Thanks to that, FIND function doesn't have a problem with finding it. So I extracted needed informations into single cells in colums D:G (Carats wasn't necessary).
Final step - after an extraction of every information I need, I concantenated them into single string in column H and then used VLOOKUP in SEARCH sheet to look for values in MATCH sheet using combinations from COMBINATIONS sheet (I created that combination in colums REF_COMBINED).

Hope, that solution Workbook will clarify what I said and hope that is what you needed

You can also build whole formula in just one column, but displaying it in separate colums is more practical IMO, because you can use it later for another searches. In single column it would look like that:

Please Login or Register  to view this content.

wow - that is some pretty serious formula work, i was miles off with my original attempt. Thank you so much Feronen, I did not expect the desired result would require so much work, you have been really very kind to help me this much, I am super grateful!