Array Formula: select given 2 criteria

mgirvin

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

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Another area formulae could be:
=INDEX(A1:D1,,MATCH(MAX((A2:D2)/(A3:D3)*100),(A2:D2)/(A3:D3)*100,0))
The result is proportional to the rating but inverse to the cost ( with same weight) and we want the maximum: Max ( Rating / Cost)
It has been multiply by 100 just to have some easier values.
 
Upvote 0
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?

Hi Mike:

one way ...

<html><head><title>Excel Jeanie HTML</title></head><body>
Excel Workbook
ABCDE
1V1V2V3V4V4
25435
3$9$8$5$7
Sheet10


</body></html>
 
Upvote 0
Dear PCL and Yogi,

I am perpetually amazed by the elegant an efficient Excel solutions that you and the rest of the MrExcel Team provide!!!!!!

Thanks for both of those amazing solutions: 1) Dividing the two is a great idea and so efficient; 2) Concatenating “V” and a MATCH result, wow!!

Thank you, Excel Masters!!!!
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top