I’m trying to make a formula that accomplishes this:

1. From a list of numbers, find the pairs of numbers with the lowest difference and second lowest difference, and write their differences elsewhere(I don’t know how to do this)
2. Use those numbers to find and use the value(a name)of the cell to their left(I don’t know how to do this with duplicate numbers) How would I go about this? The final product should include all 4 numbers, names, and both differences.

In the picture below, the pink shaded section is the goal.

CodePudding user response：

What I did is to first SORT the values:

=SORT(A2:B,2,1)

Then, wrapped it in LAMBDA (in order not to repeat those formula again and again), and started "scanning" those values to find the differences. Since it's already sorted, the "smallest" will be found in this comparison.

For this, I used MAP. Used SEQUENCE, COUNTA and INDEX in order to find the amount of values in the first column; and SEQUENCE will help me do the "navigation". With the variable resultant "p" I searched each row with the next one (p 1). Resulting in something like this:

=LAMBDA(sorted,MAP(SEQUENCE(COUNTA(INDEX(sorted,,1))-1),LAMBDA(p,{INDEX(sorted,p,1),INDEX(sorted,p 1,1),INDEX(sorted,p 1,2)-INDEX(sorted,p,2)})))(SORT(A2:B,2,1))

With that, you're already one step away. Just with SORTN you can find the four smallest differences:

=LAMBDA(diff,SORTN(diff,4,1,3,1))
(LAMBDA(sorted,MAP(SEQUENCE(COUNTA(INDEX(sorted,,1))-1),LAMBDA(p,{INDEX(sorted,p,1),INDEX(sorted,p 1,1),INDEX(sorted,p 1,2)-INDEX(sorted,p,2)})))
(SORT(A2:B,2,1)))

I hope it's already useful! You can split those values in two columns, obviously; just using INDEX to access the desired row of this formula; or just locate the formula somewhere and refer to those cells. Let me know if it's useful!

CodePudding user response：

you can try:

=LAMBDA(z,SORTN(MAP(INDEX(z,,1),INDEX(z,,2),LAMBDA(a,b,{a,b,ABS(XLOOKUP(a,A:A,B:B,)-XLOOKUP(b,A:A,B:B,))})),4,0,3,1))(INDEX(SPLIT(QUERY(UNIQUE(MAP(LAMBDA(z,FLATTEN(z& "