So col A,B,C,D, have 4 vendors V1, V2, V3, V4 with various rating based on Quality, supply time, customer service etc... which are predetermined. So we want to go with the vendor with the highest rating and lowest cost. So in this scenario excel should tell me V4 is the vendor with highest rating and lowest cost.

__|__A__|__B__|__C__|__D__|

1__|__V1_|__V2_|__V3_|__V4__|

2__|__5__|__4__|__3__|__5___| <- rating

2__|__$9_|__$8_|__$5_|__$7__| <- cost

I created this array formula:

=INDEX(A1:D1,MATCH(MIN(IF((A2:D2=MAX(A2:D2))*(A3:D3)<>0,(A2:D2=MAX(A2:D2))*(A3:D3))),A3:D3,0))

It got me the right answer, “Vender 4”, but…

Is there a more efficient and elegant formula or method that I could use?