Dec 13 2021 03:17 PM
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 Name | VM Types | |||||
1 | 2 | 3 | 4 | 5 | 6 | |
chg1 | bcw001 | acw001 | pcp001 | bcd001 | acd001 | ecd001 |
chg2 | bcw002 | acw002 | ecw001 | cpu001 | ||
chg3 | bcw003 | acw003 | ecw002 | cpu002 | ||
chg4 | bcw004 | acw004 | ecw003 | cpu003 | ||
chg5 | bcw005 | acw005 | sps001 | cpu004 | ||
chg6 | bcw006 | acw006 | sdb001 | cpu005 | ||
chg7 | bcw007 | acw007 | acw036 | nls001 | ||
chg8 | bcw008 | acw008 | acw037 | |||
chg9 | bcw009 | bcw041 | ||||
chg10 | bcw010 | bcw042 | rep001 | |||
chg11 | bcw011 | acw009 | pcp002 | bcm001 | acm001 | ecm001 |
chg12 | bcw012 | acw010 | ecw004 | cpu006 | ||
chg13 | bcw013 | acw011 | ecw005 | cpu007 | ||
chg14 | bcw014 | acw012 | cpu008 | |||
chg15 | bcw015 | acw013 | sps001 | cpu009 | ||
chg16 | bcw016 | acw014 | sdb001 | cpu010 | ||
chg17 | bcw017 | acw015 | acw038 | nls002 | ||
chg18 | bcw018 | acw016 | acw039 | alb001 | ||
chg19 | bcw019 | acw017 | acw040 | ate001 | ||
chg20 | bcw020 | bcw043 | rep002 | |||
chg21 | bcw021 | acw018 | bcm002 | jsv001 | acm002 | ecm002 |
chg22 | bcw022 | acw019 | ecw006 | cpu011 | ||
chg23 | bcw023 | acw020 | ecw007 | cpu012 | ||
chg24 | bcw024 | acw021 | ecw008 | cpu013 | ||
chg25 | bcw025 | acw022 | arp001 | cpu014 | ||
chg26 | bcw026 | acw023 | cpu015 | |||
chg27 | bcw027 | acw024 | rfw001 | |||
chg28 | bcw028 | acw025 | acw041 | anp001 | ||
chg29 | bcw029 | acw026 | acw042 | ate001 | ||
chg30 | bcw030 | bcw044 | rep003 | reg001 | ||
chg31 | bcw031 | acw027 | bcm003 | jsv002 | acm003 | ecm003 |
chg32 | bcw032 | acw028 | ecw009 | cpu016 | ||
chg33 | bcw033 | acw029 | ecw010 | cpu017 | ||
chg34 | bcw034 | acw030 | cpu018 | |||
chg35 | bcw035 | acw031 | arp002 | cpu019 | ||
chg36 | bcw036 | acw032 | cpu020 | |||
chg37 | bcw037 | acw033 | acw043 | rfw002 | ||
chg38 | bcw038 | acw034 | acw044 | anp002 | ||
chg39 | bcw039 | acw035 | acw045 | ate002 | ||
chg40 | bcw040 | bcw045 | reg002 | alb002 |
|
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 Name | Server Name |
bcw001 | chg1 |
ecw001 | chg2 |
cpu002 | chg3 |
acm001 | chg11 |
ecm002 | chg21 |
bcw045 | chg40 |
(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 Name | VM Types |
chg1 | bcw001,acw001,pcp001,bcd001,acd001,ecd001 |
chg2 | bcw002,acw002,ecw001,cpu001 |
chg3 | bcw003,acw003,ecw002,cpu002 |
chg4 | bcw004,acw004,ecw003,cpu003 |
chg5 | bcw005,acw005,sps001,cpu004 |
chg6 | bcw006,acw006,sdb001,cpu005 |
chg7 | bcw007,acw007,acw036,nls001 |
chg8 | bcw008,acw008,acw037 |
chg9 | bcw009,bcw041 |
chg10 | bcw010,bcw042,rep001 |
chg11 | bcw011,acw009,pcp002,bcm001,acm001,ecm001 |
chg12 | bcw012,acw010,ecw004,cpu006 |
chg13 | bcw013,acw011,ecw005,cpu007 |
chg14 | bcw014,acw012,cpu008 |
chg15 | bcw015,acw013,sps001,cpu009 |
chg16 | bcw016,acw014,sdb001,cpu010 |
chg17 | bcw017,acw015,acw038,nls002 |
chg18 | bcw018,acw016,acw039,alb001 |
chg19 | bcw019,acw017,acw040,ate001 |
chg20 | bcw020,bcw043,rep002 |
chg21 | bcw021,acw018,bcm002,jsv001,acm002,ecm002 |
chg22 | bcw022,acw019,ecw006,cpu011 |
chg23 | bcw023,acw020,ecw007,cpu012 |
chg24 | bcw024,acw021,ecw008,cpu013 |
chg25 | bcw025,acw022,arp001,cpu014 |
chg26 | bcw026,acw023,cpu015 |
chg27 | bcw027,acw024,rfw001 |
chg28 | bcw028,acw025,acw041,anp001 |
chg29 | bcw029,acw026,acw042,ate001 |
chg30 | bcw030,bcw044,rep003,reg001 |
chg31 | bcw031,acw027,bcm003,jsv002,acm003,ecm003 |
chg32 | bcw032,acw028,ecw009,cpu016 |
chg33 | bcw033,acw029,ecw010,cpu017 |
chg34 | bcw034,acw030,cpu018 |
chg35 | bcw035,acw031,arp002,cpu019 |
chg36 | bcw036,acw032,cpu020 |
chg37 | bcw037,acw033,acw043,rfw002 |
chg38 | bcw038,acw034,acw044,anp002 |
chg39 | bcw039,acw035,acw045,ate002 |
chg40 | bcw040,bcw045,reg002,alb002 |
Thank you guys in advance.
Dec 13 2021 04:12 PM
=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.
Dec 13 2021 09:24 PM
Dec 13 2021 09:30 PM
SolutionDec 14 2021 01:53 AM
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, ""))
)
)
Dec 14 2021 09:32 AM
Dec 14 2021 09:35 AM
Dec 14 2021 11:40 AM
Sorry, I should have defined the terms. 'VirtualMachines' refers to the source data whilst the abbreviated VM to the specific instances required.
Dec 13 2021 09:30 PM
SolutionProbably.
=INDEX($A$1:$A$40,AGGREGATE(15,6,ROW($B$1:$G$40)/($B$1:$G$40=I1),1))