Nth Smallest Value with Criteria & Highlight Bottom K Bids (SMALL + FILTER)

Choose a cutoff and a tie policy

Freight bids are 420, 380, 510 and 405. Their sorted order is 380, 405, 420, 510, so K=2 gives a cutoff of 405. SMALL counts duplicate values as separate observations; it does not return the kth distinct amount.

The flags include every bid at or below the cutoff. If several bidders tie there, more than K rows can be selected. This is intentionally a tie-inclusive shortlist, not an algorithm that chooses exactly K vendors. Define a separate tie-break rule if an exact row count is required.

Validate K and all matching bids

K in G2 must be a positive integer. The helper D column marks numeric nonnegative bids. If any selected-category bid is blank, text or negative, the cutoff shows Check category bids instead of silently excluding it. Zero is allowed as a quoted amount under this example policy.

H2 escapes criteria wildcards and operators for counting. FILTER uses literal equality; names are case-insensitive and spaces remain significant. No matching category and too few matches produce distinct messages. Invalid bids in another category do not prevent this category’s result.

=IF(OR(COUNTBLANK(F2)>0,NOT(ISTEXT(F2))),"Enter a category",IF(NOT(ISNUMBER(G2)),"Enter numeric K",IF(OR(G2<1,G2<>INT(G2)),"K must be a positive integer",IF(COUNTIF(B2:B7,H2)=0,"No matching bids",IF(COUNTIFS(B2:B7,H2,D2:D7,1)<>COUNTIF(B2:B7,H2),"Check category bids",IF(COUNTIF(B2:B7,H2)<G2,"Fewer Than K Matches",SMALL(FILTER(C2:C7,B2:B7=F2),G2)))))))

Flag every bid at or below the cutoff

K2:K7 contain the Boolean flags. The row-4 formula below is TRUE for the 380 Freight bid when K=2. The 420 Freight bid and all Packaging bids are FALSE. If J2 contains a validation message, every flag is FALSE: inspect the cutoff message before treating that as an empty shortlist.

These are formula outputs that can drive a conditional-formatting rule. The example does not itself create colored formatting or evaluate vendor suitability beyond the entered amounts.

=IF(NOT(ISNUMBER($J$2)),FALSE,AND(B4=$F$2,D4=1,C4<=$J$2))

Further reading

• Microsoft Support — SMALL function