I have a spreadsheet that has over 100 companies and their associated plans, locations and rating tiers (1-4). The user would manually select the company, plan and location and the formula would retrieve the 4 different rates. I tried using INDEX MATCH, but the problem is if the rates are not in the very first match, it retrieves $0.00 instead of the actual rate. My thought is to use AGGREGATE instead of match, but I am unsure how to create the formula.
Rate 1 Formula =INDEX($D$2:$D$23,MATCH(1,($K$1=$A$2:$A$23)*($K$2=$B$2:$B$23)*($K$3=$C$2:$C$23),0))
Rate 2 Formula =INDEX($E$2:$E$23,MATCH(1,($K$1=$A$2:$A$23)*($K$2=$B$2:$B$23)*($K$3=$C$2:$C$23),0))
Rate 3 Formula =INDEX($F$2:$F$23,MATCH(1,($K$1=$A$2:$A$23)*($K$2=$B$2:$B$23)*($K$3=$C$2:$C$23),0))
Rate 4 Formula =INDEX($G$2:$G$23,MATCH(1,($K$1=$A$2:$A$23)*($K$2=$B$2:$B$23)*($K$3=$C$2:$C$23),0))
Rate 1 Formula =INDEX($D$2:$D$23,MATCH(1,($K$1=$A$2:$A$23)*($K$2=$B$2:$B$23)*($K$3=$C$2:$C$23),0))
Rate 2 Formula =INDEX($E$2:$E$23,MATCH(1,($K$1=$A$2:$A$23)*($K$2=$B$2:$B$23)*($K$3=$C$2:$C$23),0))
Rate 3 Formula =INDEX($F$2:$F$23,MATCH(1,($K$1=$A$2:$A$23)*($K$2=$B$2:$B$23)*($K$3=$C$2:$C$23),0))
Rate 4 Formula =INDEX($G$2:$G$23,MATCH(1,($K$1=$A$2:$A$23)*($K$2=$B$2:$B$23)*($K$3=$C$2:$C$23),0))