Search code examples
excelexcel-formulatextjoin

Using TEXTJOIN to display table values of numbers within certain criteria

This is a continuation of a previous question that was solved BUT I realised I had more criteria to fulfill.

In the previous post, I received formulas that can:

Retrieve the table value for the highest value Retrieve the table value for a value within 0.02 of the highest value

As so:

Example 1

But I've realised I also need to retrieve table values for numbers that are over 0.3, as in below:

Example 2

If possible can both these criteria be combined into one formula?

I have tried the formula on the example below:

=TEXTJOIN(",",TRUE,FILTER(C14:H14,MAX(C15:H15)-C15:H15<=0.02),FILTER(C14:H14,MAX(C15:H15)-C15:H15<=0.3))

Attemptt 1

But have no idea why the results are as shown. Have played around with it, can't get it to make sense, especially coming back with the 2 in the 0,0,1,2 cell.

Solution

  • Try this one:

    =LET(header,B1:G1,
    data,B2:G5,
    BYROW(data,LAMBDA(r,
        TEXTJOIN(", ",TRUE,
            FILTER(header,(r>0.3)+(MAX(r)-r<=0.02))
            )
        )
    )
)

    enter image description here

    Row 5 is an edge example:

    • 0.31 is the max value of the row (and > 0.3)
    • 0.29 is <= 0,02 diff from 0.31