# Formula that will return a result based criteria found through searching multiple cells

1. ## Formula that will return a result based criteria found through searching multiple cells

Hi,

I've been trying to create a formula in the attached spreasheet that searches through columns A, C & D and returns the result in column E based on the criteria in red in columns A,C&D.

Also, if no criteria exists the cell should remain blank.

Any help or advice anyone can provide would be greatly appreciated.

Thank you,

2. ## Re: Formula that will return a result based criteria found through searching multiple cell

Your request is not too clear to me, sorry Perhaps you could include a few examples of your expected outcome and how you arrived atthem?

Are you, for instance, trying to find RED text in a cell and then return something based on that "find"?

3. ## Re: Formula that will return a result based criteria found through searching multiple cell

Sorry, yes, the formula should find the RED text and then return the text in BLUE in column E.

Column E is basically a list of possible returns.

There will be approx 500-1000 rows of data to that will contain various forms of the text in columns A, C & D, but the text in RED will be constant.

4. ## Re: Formula that will return a result based criteria found through searching multiple cell

Excel cannot "see" colors, so unless there is some logic to how you change the colors, you will need someone to put some VBA together for you

5. ## Re: Formula that will return a result based criteria found through searching multiple cell

The colour doesn't matter --- i just used red text to hightlight the criteria.

Essentially I need the formulas to search through columns A,C&D for any of text in the `Search for`column below and return the result in the column E from the `Return Result`column below. I was trying to use VLookup but i don`t think Vlookup can search through 3 different cells.

Search for

ROC
24200
TC
EO
ARP
NCS
CCD
48ELO
GAPOSA

Return Result
ROC
AR
TC
EO
ARP
NCS
CCD
Total
AR
AR

6. ## Re: Formula that will return a result based criteria found through searching multiple cell

I added your criteria to G2:G12, you can movethem where you want, just adjust the references in the formula...

7. ## Re: Formula that will return a result based criteria found through searching multiple cell

Ok thats great. Thanks, (I apologize for being so confusing earlier).

But I need it to search G2:G12, it looks like this formula (when i tried it anyway) was only searching for the criteria in G2?

8. ## Re: Formula that will return a result based criteria found through searching multiple cell

OK replace the formula with this array formula...
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1