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.