mgirvin
Well-known Member
- Joined
- Dec 15, 2005
- Messages
- 1,236
- Office Version
- 365
- Platform
- Windows
Dear Excel Masters,
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?
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?