Entered value in range returns multiple values

Copper Contributor

I am trying to create something so I don't have to go looking around for all the data when a value is entered in a cell. For example:

Say there is a range of cells, when a numerical value is entered into one cell I want the output to be a list of other cells and their corresponding values.


I have been able to do what I am talking about with this formula: 

=If(ISNUMBER(J32), Textjoin(" - ", True, J1,J32,A32))


The problem with this is it is only looking at one cell for a input. I want to scan a range of cells, like (G31:K41). 


Anyone know how to scan a range and return the specific info I am looking for when one of those cells in the range has a value?

1 Reply


=IF(REDUCE(0,TOCOL(B2:C8),LAMBDA(ini,arr,N(ISNUMBER(arr))+ini)),TEXTJOIN(" - ", , J1,G7,A7),"")

You can try this formula. If there is at least one cell with a number in range B2:C8 then the TEXTJOIN is returned in cell D4 otherwise D4 is blank.