Using IF ISNUMBER SEARCH with AND

Hello and good morning to all,

I have very limited ability with excel although I do try, I have got a bit stuck and was hoping that I could perhaps get some help please.

I am trying to create a formula using IF, ISNUMBER, SEARCH with AND

What I am trying to do.

I have an excel field with several coma separated items, and another field that I will use to display in this example various colours.

What I would like to do is display an answer in another field if two conditions are met, for example if the field A1 contains the word "Estate Car" (anywhere in the field, these are coma separated, for example "Estate Car, Saloon Car, Sports Car,) and the Field A2 contains "RED" I would like Field A3 to display "Estate Car - Red" - If Field A1 contains the word "Estate Car" and Field A2 Contains "Yellow" Field A3 displays "Estate Car - Yellow"

If the Field A1 does not contain "Estate Car" then the filed is left blank.

I have been trying various formulas without success: =IF(ISNUMBER(SEARCH(AND("Estate Car",A2,B2 = Red)),"Estate Car - Red","")))

I thank you in advance for any help anyone can provide, thank you

Attach a sample workbook (not image).

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Hello,

thank you for your reply, as you request I have "mocked up" an example of what I am needing.

As you can see, in field A2 - A6 there are various "Products", in this case car types, all coma separated, in column B (Colour) we have two different colours (Red & Yellow). Columns C, D, and E are the output fields where the proposed formulas will go.

Field C2 is looking for the Product "Estate Car" anywhere in Field A2, as it is there it also now looks to see if there is a colour in field B2 it is "RED" so it displays "Estate Car - Red".

Field D2 is looking for "Saloon Car" as it is not in A2 it is left blank, E2 is looking for "Sports car" as it is contained within A2 it looks for a colour in B2 and displays "Sports Car - Red",

The one below are variations but the same rules apply it looks first first for the product anywhere in the first field then looks for a could and displays in the same format if the conditions are met.

I hope this will now clarify what I am looking for ,

thank you again for your help.

Field D2 is looking for "Saloon Car" as it is not in A2 it is left blank

All car types are ALL column A entries

Try

=IF(ISNUMBER(SEARCH(C$1,$A2)),C$1&IF($B2="","","-"&$B2))

See attached

Hello

thank you very much for your response, I must admit that I uploaded the wrong excel sheet, the Car types will not always be the same and some types will be omitted in certain rows, (which I did show in my excel sheet that I did not upload correctly, I have attached it now).

This works in a different manner to which I had envisaged, I was thinking that rather than "Insert" the colour from column B, it would just not show anything if it was not in the field.

I think your version is better and will work better for me and give more flexibility!

The only thing that I was wondering is if it could be changed is that if there was no match or the criteria was not met, then the result field be "blank" or empty, at the moment where the conditions are not met it shows "False", also if I am not asking too much, could you just explain how the formula is working so I can understand it and hopefully learn from your help.

Thank you very much for your help, very gratefully received, whilst I knew what I was trying to do I was going round in circles, with your help I am now able to progress, thank you again and thank you to the forum for providing an excellent platform.

(I have attached my original file just for reference.)

Try

=IF(ISNUMBER(SEARCH(C$1,$A2)),C$1&IF($B2="","","-"&$B2),"")

NOTE: I removed "& colour" from the headings"

Hello

Thank you for the update, I have tried the revised and I have worked out how part of what the formula is working(!), if the colour is omitted, it still shows the car type which is not what I am trying to achieve, I only want a result if BOTH conditions are met, so The car type must be listed in the relevant row field and the colour must be in the relevant row colour field (this is because each row is a unique record), this will be used for mail-merge, so am looking for both criteria to be matched otherwise the field needs to be empty, (blank)

I think it is almost there but just needs to be simpler if possible (so an old man like me can understand it.

many thanks again for your help and perseverance with me.

Hello,

Just a quick line to say thank you for your help, I am sorry I didn't get back to you yesterday, I have had a play with the formula and I think the way it works is actually better that the way I envisaged, it gives me more options. I am using this as part of the data for a mail merge with rules from within word, so I can set up almost an infinite number of canned responses, many thanks for your help.

You are very welcome.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.