Forum Discussion

swstr510's avatar
swstr510
Copper Contributor
May 24, 2021

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-...
Excel for web
Excel on Mac
Formulas and Functions
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
May 24, 2021

swstr510 

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) 
  )
swstr510's avatar
swstr510
Copper Contributor
May 25, 2021

PeterBartholomew1 

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 .

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    May 25, 2021

    swstr510 

    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})
  )

     

Resources