Forum Discussion
Index match Function
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})
)
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 PeterBartholomew1
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 .
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.