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 which identifies the name of the server and VM Types column, which identifies the different types of VMs on that server. For example, Server "chg1" has 6 VMs on that host with the names identified in the sheet here.

Server NameVM Types
 123456
chg1bcw001acw001pcp001bcd001acd001ecd001
chg2bcw002acw002ecw001cpu001  
chg3bcw003acw003ecw002cpu002  
chg4bcw004acw004ecw003cpu003  
chg5bcw005acw005sps001cpu004  
chg6bcw006acw006sdb001cpu005  
chg7bcw007acw007acw036nls001  
chg8bcw008acw008acw037   
chg9bcw009bcw041    
chg10bcw010bcw042rep001   
chg11bcw011acw009pcp002bcm001acm001ecm001
chg12bcw012acw010ecw004cpu006  
chg13bcw013acw011ecw005cpu007  
chg14bcw014acw012 cpu008  
chg15bcw015acw013sps001cpu009  
chg16bcw016acw014sdb001cpu010  
chg17bcw017acw015acw038nls002  
chg18bcw018acw016acw039alb001  
chg19bcw019acw017acw040ate001  
chg20bcw020bcw043rep002   
chg21bcw021acw018bcm002jsv001acm002ecm002
chg22bcw022acw019ecw006cpu011  
chg23bcw023acw020ecw007cpu012  
chg24bcw024acw021ecw008cpu013  
chg25bcw025acw022arp001cpu014  
chg26bcw026acw023 cpu015  
chg27bcw027acw024 rfw001  
chg28bcw028acw025acw041anp001  
chg29bcw029acw026acw042ate001  
chg30bcw030bcw044rep003reg001  
chg31bcw031acw027bcm003jsv002acm003ecm003
chg32bcw032acw028ecw009cpu016  
chg33bcw033acw029ecw010cpu017  
chg34bcw034acw030 cpu018  
chg35bcw035acw031arp002cpu019  
chg36bcw036acw032 cpu020  
chg37bcw037acw033acw043rfw002  
chg38bcw038acw034acw044anp002  
chg39bcw039acw035acw045ate002  
chg40bcw040bcw045reg002alb002 

 

 

 

 

I have another sheet (output sheet), where I have the VM Names in the first column and the second column should identify the corresponding server name, example below

 

VM NameServer Name
bcw001chg1
ecw001chg2
cpu002chg3
acm001chg11
ecm002chg21
bcw045chg40

(i truncated the output)

 

I can also work with the following table format as the input (if it makes any easier), same output table is expected.

Server NameVM Types
chg1bcw001,acw001,pcp001,bcd001,acd001,ecd001
chg2bcw002,acw002,ecw001,cpu001
chg3bcw003,acw003,ecw002,cpu002
chg4bcw004,acw004,ecw003,cpu003
chg5bcw005,acw005,sps001,cpu004
chg6bcw006,acw006,sdb001,cpu005
chg7bcw007,acw007,acw036,nls001
chg8bcw008,acw008,acw037
chg9bcw009,bcw041
chg10bcw010,bcw042,rep001
chg11bcw011,acw009,pcp002,bcm001,acm001,ecm001
chg12bcw012,acw010,ecw004,cpu006
chg13bcw013,acw011,ecw005,cpu007
chg14bcw014,acw012,cpu008
chg15bcw015,acw013,sps001,cpu009
chg16bcw016,acw014,sdb001,cpu010
chg17bcw017,acw015,acw038,nls002
chg18bcw018,acw016,acw039,alb001
chg19bcw019,acw017,acw040,ate001
chg20bcw020,bcw043,rep002
chg21bcw021,acw018,bcm002,jsv001,acm002,ecm002
chg22bcw022,acw019,ecw006,cpu011
chg23bcw023,acw020,ecw007,cpu012
chg24bcw024,acw021,ecw008,cpu013
chg25bcw025,acw022,arp001,cpu014
chg26bcw026,acw023,cpu015
chg27bcw027,acw024,rfw001
chg28bcw028,acw025,acw041,anp001
chg29bcw029,acw026,acw042,ate001
chg30bcw030,bcw044,rep003,reg001
chg31bcw031,acw027,bcm003,jsv002,acm003,ecm003
chg32bcw032,acw028,ecw009,cpu016
chg33bcw033,acw029,ecw010,cpu017
chg34bcw034,acw030,cpu018
chg35bcw035,acw031,arp002,cpu019
chg36bcw036,acw032,cpu020
chg37bcw037,acw033,acw043,rfw002
chg38bcw038,acw034,acw044,anp002
chg39bcw039,acw035,acw045,ate002
chg40bcw040,bcw045,reg002,alb002

 

Thank you guys in advance.

 

excel
Excel on Mac
Formulas and Functions
office 365

8 Replies

  • 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, ""))
    )
  )

     

     

    • Vijay_Uppala's avatar
      Vijay_Uppala
      Copper Contributor
      Dec 14, 2021
      PeterBartholomew1
      Thank you for the response.
      I couldnt get the 365 notation to work as well.
      I'm assuming the virtualmachine is the named range for the names in columns J, VM is the is the array of all VMs (B1 to G40) and server is the array of A1 to A40. If so, i couldnt get this to work.
      Let me know if i got this wrong.
  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Dec 14, 2021

    Vijay_Uppala 

    =IF(NOT(ISNA(INDEX($A$1:$A$40,MATCH(I1,$B$1:$B$40,0)))),INDEX($A$1:$A$40,MATCH(I1,$B$1:$B$40,0)),IF(NOT(ISNA(INDEX($A$1:$A$40,MATCH(I1,$C$1:$C$40,0)))),INDEX($A$1:$A$40,MATCH(I1,$C$1:$C$40,0)),IF(NOT(ISNA(INDEX($A$1:$A$40,MATCH(I1,$D$1:$D$40,0)))),INDEX($A$1:$A$40,MATCH(I1,$D$1:$D$40,0)),IF(NOT(ISNA(INDEX($A$1:$A$40,MATCH(I1,$E$1:$E$40,0)))),INDEX($A$1:$A$40,MATCH(I1,$E$1:$E$40,0)),IF(NOT(ISNA(INDEX($A$1:$A$40,MATCH(I1,$F$1:$F$40,0)))),INDEX($A$1:$A$40,MATCH(I1,$F$1:$F$40,0)),IF(NOT(ISNA(INDEX($A$1:$A$40,MATCH(I1,$G$1:$G$40,0)))),INDEX($A$1:$A$40,MATCH(I1,$G$1:$G$40,0))))))))

     

    Is this what you are looking for? There is probably an easier way to achieve this but this is my best solution at the moment.

      • Vijay_Uppala's avatar
        Vijay_Uppala
        Copper Contributor
        Dec 14, 2021
        Detlef_Lewin
        This worked like charm and also addresses my concerns of adding more columns in future.
        Thank you very much.
    • Vijay_Uppala's avatar
      Vijay_Uppala
      Copper Contributor
      Dec 14, 2021
      @Qadruple_Pawn
      Thank you very much for the reply.
      This definitely works, I was hoping to avoid multiple queries, one for each column as I might to have more columns in future. I'm not sure if there is a easier way (xlookup, index/match combo or others?) that can be used.

Resources