Index match Function

Copper Contributor

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

placevendor-1vendor-2vendor-3vendor-4selection-1selection-2
USA5000700050008000vendor-1vendor-3
India9000800080009500vendor-2vendor-3
10 Replies

* What happens if there are 3 or more vendors that match the minimum value?
* You will be much better off if you can reorganize the data to the format

placevendorvalue
usavendor-14000
usavendor-22000
etc.  

 

If you can do that, there are fairly straightforward approaches to consider.

@swstr510 

@swstr510 

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.

@Peter Bartholomew 

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.

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

@Peter Bartholomew 

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 .

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

 

@Peter Bartholomew 

I applied the same logic but didn't work, as check the attached sheet, where AG column highlighted with red color are the error as no rate comes in L-1 .

@Hans Vogelaar @Peter Bartholomew 

 

i have attached a file, i need min rates L-1,L-2,L-3, L-4 RATES, FROM VENDORS RATES G180:AF180 ROWS RANGE, AND ON BASIS OF MIN RATE, VENDORS NAME SHOULD BE REQUIRED .

@Hans Vogelaar @Peter Bartholomew

 have attached a file, i need min rates L-1,L-2,L-3, L-4 RATES, FROM VENDORS RATES G180:AF180 ROWS RANGE, AND ON BASIS OF MIN RATE, VENDORS NAME SHOULD BE REQUIRED .

@swstr510 

The text fields weren't ranked as I expected.  The attached contains a test to eliminate text fields from consideration and may behave a little better.  

 

Basically I switched to Excel 365 to avoid traditional spreadsheet formulas like this so I am probably not the best person to perfect the formulas.