Array Formula: select given 2 criteria

mgirvin

Well-known Member
Joined
Dec 15, 2005
Messages
1,213
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?
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

PCL

Well-known Member
Joined
Jul 15, 2008
Messages
1,348
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.
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
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>
 

mgirvin

Well-known Member
Joined
Dec 15, 2005
Messages
1,213
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!!!!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,255
Messages
5,600,554
Members
414,388
Latest member
Pkmep4

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
Top