Search Through a Column to Find Match, ignore blanks return Value

[lmc2016]

Hi All,

This has been confusing me all morning. I have a table of different shops and depending on the order value the order # will be on a different commission tier.

I want to setup a dropdown so I can check the different tiers the shop is on.

VLOOKUP, but that only returns the first match of the shop, so in this instant it is correct for Tier 1, but it won't find Tier 2 or 3 as it returns the match in B:B first.

Index Match If (ignore blank), tried this and it returns Tier 1 but not the other tiers and Index Match has the same result.

ExcelProblem.PNG

Any help would be great. Thanks.