Searching for specific field within range

[ExcelSponge]

Hello all,

I am having a difficult time finding a specific value within a range of cells. I attached a sample spreadsheet showing the top 3 soda brands by region (purely hypothetical). In the example I want to report on Pepsi. If it is included in the top 3, then I don't need to show an additional field. But if it is not in the top 3, then I want to show a 4th row with Pepsi reported.

Thanks!
Scott

Test - Company Ranking.xlsx

[daffodil11]

Theres a number of formulas you'd use. See attached.

The base formula is =IF(COUNTIF($B15:$B17,$C$4),"",other formula) for row 11.

From there copy to 18 and 25 and you're set.

[ExcelSponge]

Easy enough - thanks!