URGENT: How to match a text cell with a list of texts and return a desired string of text

Hi all, I have been trying to solve this all day without success. Hopefully some one can help me out, I highly appreciate it!

ExcelHelp.PNG

Please refer to the picture or excel file.

Problem: I need a formula in the yellow area where it can look at each grocery bag, and satisfy the condition below:

Condition: If the _#B_#B (or) KAPOW appears in Grocery Bag 1 , then it is Juicy.
If the _#B_#B (or) KAPOW appears in Grocery Bag 2, then it is Rotten
If NEITHER _#B_#B (or) KAPOW appears in Grocery Bag 1 or 2, then it is NO MATCH!

Table
Type (Juicy or Rotten or No Match) Grocery Bag 1 Grocery Bag 2
should return JUICY BigApple_3B_3B BigApple
should return JUICY BigApple_6B_6B BigApple
should return JUICY BigOrange_7B_7B BigOrange
should return ROTTEN BigApple KAPOW
should return ROTTEN BigOrange KAPOW
should return JUICY KAPOW Big Apple
should return NO MATCH JBABY JBABY
should return ROTTEN BigBanana BigBanana_5B_5B
should return ROTTEN BigGrape BigGrape_10B_10B



Thanks!!!

Welcome to the forum.

If your requirement is urgent, you should take the opportunity to read the forum rules about how to get your questions answered quickly.

Try this in B10

Please Login or Register  to view this content.

and copy down.

Is this what you are trying to achieve?

You're welcome and thanks for the feedback. I depends on what are your additional criteria and how do you arrange a list of those words. Better if you upload another workbook with all the possible criteria with proper set up and let me see if I can provide you any further assistance.

Try this ...

Formula:

Please Login or Register  to view this content.

Check the attached file..

Other than the formula suggested by Vikas, you may also try this Array Formula. Remember Array Formula requires special keystrokes Ctrl+Shift+Enter instead of just Enter which you normally use in case of a regular formula. i.e. after typing the formula in the formula cell, don't press Enter alone but hold down Ctrl+Shift together and then press Enter to confirm the formula as an array formula. If an array formula is confirmed correctly, you will notice in the formula bar that the formula gets surrounded by the Curly Brackets {formula} and if you don't see the curly bracket that simple means the formula is not entered as an array formula. In that case select the cell with the formula, press F2 (Function Key) and then hold Ctrl+Shift together and then press Enter.

In B23

Please Login or Register  to view this content.

and then copy down.

You're welcome.