Problem with Errors in Formula (Too many arguments)

Hello,

I am creating a price comparison of four carriers. My spreadsheet lists 9911 origin/ destination combinations. Each carriers has provided nine weight categories. I would like to create a formula that will compare each carriers price and return:



1.) The lowest rate by weight class

2.) The carriers name, providing the lowest rate.

I realize that this may be a bit confusing, so I am attaching a close replica of the spreadsheet I am working on.


Thank you in advance, for you help.

Sincerely,

Bitty

In your spreadsheet, the destination is ATL but there are no ATL destinations.

Should there also be an origin to lookup?

It looks like you are trying to find the minimum rate per company and weight, for each match of Orig/Dest.

Once that is confirmed, I'd insert extra columns to make every company's data set match and then you'd most likely use a sumproduct/min/mod formula to pull the data of every nth column and find the minimum value > 0. (To skip company's with blanks)

I've run into a wall so far.

I got as far as:

=MIN((MOD(COLUMN(OFFSET(C3:AD3,MATCH(AO1&AO2,A3:A31&B3:B31,0)-1,MATCH(AO3,$C$2:$K$2,0)-1)),9)=MOD(MATCH(AO3,$C$2:$K$2,0)+2,9)*(OFFSET(C3:AD3,MATCH(AO1&AO2,A3:A31&B3:B31,0)-1,MATCH(AO3,$C$2:$K$2,0)-1))))

Struggling to force the arrays to give the answer of 21.43, though I can make it do it manually by calculating

MOD(COLUMN(OFFSET(C3:AD3,MATCH(AO1&AO2,A3:A31&B3:B31,0)-1,MATCH(AO3,$C$2:$K$2,0)-1)),9)=MOD(MATCH(AO3,$C$2:$K$2,0)+2,9)

and then

(OFFSET(C3:AD3,MATCH(AO1&AO2,A3:A31&B3:B31,0)-1,MATCH(AO3,$C$2:$K$2,0)-1))

and multiplying the sets against one another.

well i tried this
in ao4
=MIN(IF((INDEX($C$3:$AL$31,MATCH($AO$1&$AO$2,INDEX($A$3:$A$31&$B$3:$B$31,0),0),0)>0)*($C$2:$AL$2=$AO$3),INDEX($C$3:$AL$31,MATCH($AO$1&$AO$2,INDEX($A$3:$A$31&$B$3:$B$31,0),0),0))) array enterred
and in a05
=LOOKUP(MATCH(AO4,INDEX($C$3:$AL$31,MATCH($AO$1&$AO$2,INDEX($A$3:$A$31&$B$3:$B$31,0),0),0),0),{1,10,19,28},{"company 1","company 2","company 3","company 4"})

Try this array formula in AO4

=MIN(IF((A3:A31=AO1)*(B3:B31=AO2)*(C3:AL31<>"")*(C2:AL2=AO3),C3:AL31))

and this one in AO5

=INDEX(C1:AL1,FLOOR(MATCH(AO4,INDEX(C3:AL31,MATCH(1,(A3:A31=AO1)*(B3:B31=AO2),0),0),0)-1,9)+1)

both confirmed with CTRL+SHIFT+ENTER