Forum Discussion

Palazques's avatar
Palazques
Copper Contributor
Apr 26, 2023

Entered value in range returns multiple values

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?

excel
Formulas and Functions
  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Apr 26, 2023

    Palazques 

    =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.

Resources