Forum Discussion

Vijay_Uppala's avatar
Vijay_Uppala
Copper Contributor
Dec 13, 2021
Solved

Lookup for a value in an array

Hello, I have a use case where I have to look up for a value that can be in an array and output a value from the corresponding output array. Here is my input table.  I have a Server Name column whi...
excel
Excel on Mac
Formulas and Functions
office 365
  • Detlef_Lewin's avatar
    Detlef_Lewin
    Dec 14, 2021

    OliverScheurich 

    Probably.

    =INDEX($A$1:$A$40,AGGREGATE(15,6,ROW($B$1:$G$40)/($B$1:$G$40=I1),1))
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Dec 14, 2021

Vijay_Uppala 

Using 365 notation

= CONCAT(IF(virtualMachine=@VM, server, ""))

or to return the servers as an array 

= MAP(VM,
    LAMBDA(v,
      CONCAT(IF(virtualMachine=v, server, ""))
    )
  )

 

 

Resources