I have a very large, year long, data collection that has money, miles, companies, and categories in it. I need a formula that compares two cells in one row and outputs a value in that same row in another cell.



1. Column K is for category (Auto, Home, Dining, Medical, etc....) TEXT
2. Column D is for company names (100's of company names possible) TEXT
3. Column M will be for miles driven (total miles to the company site) NUMBER
4. The formula is only for one row at a time.

Criterial to formula:
1. if cell K is NOT = "Medical" then we don't care about cell D and cell M's output should be blank.

2. if cell K = "Medical" then also look in cell D and output # of miles driven in cell M based on the "text" within cell D.

3. Cell D will have multiple companies at 2 miles, and another set at 5 miles, and another set at 10 miles, and so on.

Here is what I have so far for Cell M2: (ROW 1 is for headers only)

=IF(K2="Medical",SUBSTITUTE(TRIM(IF(COUNT(SEARCH({ "A","B","C","D","E"},D2)),10,"") &IF(COUNT(SEARCH({"F","G","H","I"},D2)),5,"") &IF(COUNT(SEARCH({"J","K","L","M"},D2)),7,"")), " ",","))

As long as K2 = "Medical" I get the number in cell M2 that matches the company name from cell D2 (for this example I used the letters A-M to keep company names short)

However, the problem is that I get the word FALSE in cell M2 if K2 is NOT = "Medical". I need it to be blank so I can add column M later.

I hope that makes sense, any help is greatly appreciated.

You are almost there.

Try changing K2="Medical", SUBSTITUTE.... to
K2<>"Medical","",SUBSTITUTE....
and if you want the output as number then precede the first IF with --

Thank you for the reply.

I changed my original formula in two ways:

1. =IF(K2<>"Medical","",SUBSTITUTE(TRIM(IF(COUNT(SEAR CH({ "A","B","C","D","E"},D2)),10,"") &IF(COUNT(SEARCH({"F","G","H","I"},D2)),5,"") &IF(COUNT(SEARCH({"J","K","L","M"},D2)),7,"")), " ",","))
1a. This now gives blanks if column K if NOT = "Medical" - Good
1b. This now gives the proper number output in M, but the #'s 10, 5, or 7 are in TEXT form as they are shifted to the left side of the M column cells.
1c. This formula does NOT allow me to add up column M when finished with all entries; it gives a 0.00 result (End of column is just =SUM(M2:M2034))

So then added the following as you noted:

2. =--IF(K2="Medical","",SUBSTITUTE(TRIM(IF(COUNT(SEARCH ({ "A","B","C","D","E"},D2)),10,"") &IF(COUNT(SEARCH({"F","G","H","I"},D2)),5,"") &IF(COUNT(SEARCH({"J","K","L","M"},D2)),7,"")), " ",","))
2a. This outputs the proper NUMBER (10, 7, or 5) if "Medical" in column K - good
2b. This also gives a #VALUE output in Column M if column K is not "Medical" - Bad.
2c. Then as expected the Sum of column M = #VALUE


I then tried the following (added a zero between ""):

3. =--IF(K2="Medical","0",SUBSTITUTE(TRIM(IF(COUNT(SEARC H({ "A","B","C","D","E"},D2)),10,"") &IF(COUNT(SEARCH({"F","G","H","I"},D2)),5,"") &IF(COUNT(SEARCH({"J","K","L","M"},D2)),7,"")), " ",","))

3a. This gives a 0.00 valve for every M column cell that does not have "Medical" in Column K. **This is fine, it just doesn't look very clean.
3b. All column M results are correct (10, 7, or 5) if K="Medical" and if Column D has a value listed.
3c. However, if column K = Medical, and column D does not contain one of the values (A-M from the example) then I get a #VALUE. (IN TURN I get a total showing #Value)

Overall these are the closest results I have, and in case 3 it is not bad to get the #VALUE listed as it tells me that company is not in my A-M list within the formula, and that I need to add it.

Not ideal, not real clean looking, but it is working for now.

Let me know if you have any other ideas, and if not then that's okay also. I'll just keep plugging away at it over time.
Thanks for the help, you got me on a better path.

You are right.
Try to change the formula to:

=IF(K2<>"Medical","",
--SUBSTITUTE(TRIM(IF(COUNT(SEARCH({"A","B","C","D"," E"},D2)),10,"") &IF(COUNT(SEARCH({"F","G","H","I"},D2)),5,"") &IF(COUNT(SEARCH({"J","K","L","M"},D2)),7,"")),"", ","))

I had an idea as you asked for.
Have you any response?