Forum Discussion
Index match Function
please help me out, i need a formula to select column name, on basis of finding the min value .
i need a formula to selection-1 and selction-2 for selecting vendor with min values
| place | vendor-1 | vendor-2 | vendor-3 | vendor-4 | selection-1 | selection-2 |
| USA | 5000 | 7000 | 5000 | 8000 | vendor-1 | vendor-3 |
| India | 9000 | 8000 | 8000 | 9500 | vendor-2 | vendor-3 |
10 Replies
I prefer the simplicity of the 365 array solution but that is only any good if you have access to the app.
Otherwise you should be able to use something like
= INDEX( availableRates, MATCH( 1, 1 + COUNTIFS(availableRates,"<"&availableRates) + COUNTIFS(availableRates,availableRates, vendor,"<"&vendor), 0) )everything is ok in your normal excel formula, but problem occurs if any vendor give no rates or 0 then they should not be counted, like i highlighted in yellowed color .
I only develop for 365 these days, having disposed of earlier versions of Excel as unfit for purpose! However, casting my mind back to how I used to do these things, it would be relatively easy to test for zero rates. At present, I interpret a zero rate as an offer to carry the goods without charge (as opposed to a blank field or any text field that would lead to the vendor being eliminated from consideration).
To reverse this it would be necessary to add a specific test for zero rates
= INDEX( availableRates, MATCH(1, 1 + COUNTIFS(availableRates, "<"&availableRates, availableRates, ">0") + COUNTIFS(availableRates, availableRates, vendor, "<"&vendor), 0) )In 365, this requires a FILTER
= LET( sortedRates, SORTBY(availableRates,availableRates), sortedVendors, SORTBY(vendor, availableRates), stacked, IF({1;0}, sortedRates, sortedVendors), filtered, FILTER(stacked, sortedRates>0), INDEX(filtered, {1,2,1,2}, {1,1,2,2}) )
If you wish to retain the crosstab layout and have Excel 365 you can return a sorted list of vendors using the SORTBY function
= LET( valuesRanked, SORTBY(Values[#Headers],Values[@]), INDEX(valuesRanked, {1,2}))With older versions of Excel one is faced with ranking the values, using COUNTIFS as a tie-breaker, and then using MATCH/INDEX to lookup the header.
Note. I paced the value columns in a Table in order to provide structured references to the data.
i have attached a file, my problem is when two vendors have same rate then it should come in O column, like in Australia rate are 13000, but of different vendors but in my formula same Logistics vendor -3 occurs, i want in O column Logistics vendor 5 should come.
