Array Formula: select given 2 criteria

mgirvin

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

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Sort by date Sort by votes
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
mgirvin said:
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?
Click to expand...

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
You must log in or register to reply here.

Forum statistics

Threads
1,214,561
Messages
6,120,239
Members
448,951
Latest member
jennlynn

Share this page

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