Forum Discussion

sam2021's avatar
sam2021
Copper Contributor
Mar 25, 2021
Solved

search between columns

I has this truncate table show below: Device is from 1 to few hundreds. Vref is from 1.0 to 5.0 Current starts from 0 then having some values and then goes down to 0. I need to return a Vref valu...
Formulas and Functions
Macros and VBA
  • SergeiBaklan's avatar
    SergeiBaklan
    Mar 26, 2021

    sam2021 

    Depends on which Excel you are. For 365 it could be

    with

    =IFNA(
   IF({1,0},
      FILTER($B$4:$B$33,($C$3:$C$32=0)*($C$4:$C$33<>0)),
      FILTER($B$4:$B$33,($C$4:$C$33<>0)*($C$5:$C$34=0)*($C$5:$C$34<>""))
),"")

     

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Mar 26, 2021

sam2021 

That's first part of formula from previous sample

=IFNA( FILTER($B$4:$B$33,($C$3:$C$32=0)*($C$4:$C$33<>0)),"")
sam2021's avatar
sam2021
Copper Contributor
Mar 31, 2021

SergeiBaklan 

Thanks Sergei. The long formula seems to partially work in my case.

1/ How can I make it to work for say 20 devices?

2/ How can I expanse for it to work if I have more Vref values per device? Currently there are 11 values. What if there are 20? I try to change the row index accordingly but it fails. Please see file attach.

Resources