This Excel lambda function provides a ranking mechanism similar to SQL's DENSE_RANK, but follows the pattern of RANK.AVG and RANK.EQ

excel-lambda-RANK.DENSE.txt

/*
RANK.DENSE

Ranks a one-dimensional array using the provided sort order. If adjacent items have the same value, they are all given the rank of the first of them. 
Ranks following equally ranked groups increment by one from the most recent rank. This is as opposed to RANK.EQ where following ranks are given the rank
they would have received if the same-ranked items were given different ranks. As such, this function behaves similarly to SQL's DENSE_RANK

Inputs:
  Number - A number or array of numbers to find the rank for from the ranks given by Ref sorted by Order
  Ref - A list of numbers to be ranked, from which the rank of Number will be found
  Order - an optional integer indicating whether the data should be ranked in descending order (0 - zero) or ascending order (1). 
          If no value is provided, the default is zero.
  
Returns:
  An array the same size as Number, with each element being an integer representing the rank of the element of Number according to the ranks of Ref when sorted by Order

*/

RANK.DENSE =LAMBDA(Number,Ref,[Order],
  LET(
    _order,IF(ISOMITTED(Order),-1,IF(Order=0,-1,1)),
    _n,Number,
    _r,INDEX(IF(ROWS(Ref)=1,TRANSPOSE(Ref),Ref),,1),
    _d,SORT(_r,1,_order),
    _i,SEQUENCE(ROWS(_d)),
    _ranks,SCAN(0,_i,
          LAMBDA(a,b,
            IFS(
              b=1,1,
              INDEX(_d,b-1,1)=INDEX(_d,b,1),a,
              TRUE,a+1
            )
          )
         ),
    _out,MAP(_n,LAMBDA(x,XLOOKUP(x,_d,_ranks,"No rank"))),
    _out
  )
);