Index, match issues with a table

[fc_mojo]

Trying to perform an index and match based on partner name. If name is found then look to see if they have services in Agriculture (and then Airway, Smart Building, etc.) from table1[[V1]:[V4]]. If found return partner name.

[TABLE="width: 0"]
<tbody>[TR]
[TD]Company
[/TD]
[TD]Agriculture
[/TD]
[TD]Airway
[/TD]
[TD]Smart Building
[/TD]
[TD]Smart City
[/TD]
[/TR]
[TR]
[TD]Comp1
[/TD]
[TD]=INDEX(Table1[Partner],MATCH($A$1,Table1[Partner],0),SEARCH(B1,Table1[[V1]:[V4]],0))
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Comp2
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Comp3
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Comp4
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]


Table1:
[TABLE="width: 0"]
<tbody>[TR]
[TD]Partner
[/TD]
[TD]V1
[/TD]
[TD]V2
[/TD]
[TD]V3
[/TD]
[TD]V4
[/TD]
[/TR]
[TR]
[TD]Comp1
[/TD]
[TD]Smart City
[/TD]
[TD]Internet of Things
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Comp2
[/TD]
[TD]Digital Signage
[/TD]
[TD]Gaming and Entertainment
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Comp3
[/TD]
[TD]Agriculture
[/TD]
[TD]Automotive and Transportation
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Comp4
[/TD]
[TD]Industrial Automation and Energy
[/TD]
[TD]Internet of Things
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Comp5
[/TD]
[TD]Retail
[/TD]
[TD]Retail Analytics
[/TD]
[TD]Smart City
[/TD]
[TD]Smart Building
[/TD]
[/TR]
[TR]
[TD]Comp6
[/TD]
[TD]Industrial Automation and Energy
[/TD]
[TD]Internet of Things
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]


Thanks Again,

 

[Col Delane]

An interesting but tricky problem - though I'm not sure I fully understand what you're trying to achieve.

It may help if you populate your first table with the results you're expecting to know what the desired result looks like.

Questions:

1. Are the companies (Comp1, Comp2, etc.) listed in the first table (Company) the same companies listed in Table1 (Partners)?
2. If so, are you just looking to create a ready-reckoner matrix of Partners x Services because not all partners offe all services and as a result the services displayed in the Partner table (Table1) are not in the same columns?
3. If not, what result do you expect when more than one Partner company offers one of your target services? For example, both Partner Comp1 and Comp5 offer Smart City.

 

[Aladin Akyurek]

Try to post the expected values for comp1 regarding agriculture, airway, etc. [instead of a (non-working) formula].

 

[fc_mojo]

An interesting but tricky problem - though I'm not sure I fully understand what you're trying to achieve.

It may help if you populate your first table with the results you're expecting to know what the desired result looks like.

Questions:

1. Are the companies (Comp1, Comp2, etc.) listed in the first table (Company) the same companies listed in Table1 (Partners)? YES

2. If so, are you just looking to create a ready-reckoner matrix of Partners x Services because not all partners offer all services and as a result the services displayed in the Partner table (Table1) are not in the same columns.
3. If not, what result do you expect when more than one Partner company offers one of your target services? For example, both Partner Comp1 and Comp5 offer Smart City.

Reply: I will use a companies name to perform a lookup in the excel table to see if the company performs services in the vertical (Agriculture, which of these companies offer service in the Agriculture, Airway, Smart Building or Smart City. Here is the expected outcome:
​

[TABLE="width: 885"]
<colgroup style="background-attachment: scroll; background-clip: border-box; background-color: transparent; background-image: none; background-origin: padding-box; background-position-x: 0%; background-position-y: 0%; background-repeat: repeat; background-size: auto; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-size-adjust: none; font-stretch: 100%; font-style: normal; font-variant: normal; font-weight: 400; line-height: normal; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;"><col style="background-attachment: scroll; background-clip: border-box; background-color: transparent; background-image: none; background-origin: padding-box; background-position-x: 0%; background-position-y: 0%; background-repeat: repeat; background-size: auto; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-size-adjust: none; font-stretch: 100%; font-style: normal; font-variant: normal; font-weight: 400; line-height: normal; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;"><col style="background-attachment: scroll; background-clip: border-box; background-color: transparent; background-image: none; background-origin: padding-box; background-position-x: 0%; background-position-y: 0%; background-repeat: repeat; background-size: auto; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-size-adjust: none; font-stretch: 100%; font-style: normal; font-variant: normal; font-weight: 400; line-height: normal; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;"><col style="background-attachment: scroll; background-clip: border-box; background-color: transparent; background-image: none; background-origin: padding-box; background-position-x: 0%; background-position-y: 0%; background-repeat: repeat; background-size: auto; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-size-adjust: none; font-stretch: 100%; font-style: normal; font-variant: normal; font-weight: 400; line-height: normal; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;"><col style="background-attachment: scroll; background-clip: border-box; background-color: transparent; background-image: none; background-origin: padding-box; background-position-x: 0%; background-position-y: 0%; background-repeat: repeat; background-size: auto; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-size-adjust: none; font-stretch: 100%; font-style: normal; font-variant: normal; font-weight: 400; line-height: normal; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;"><col style="background-attachment: scroll; background-clip: border-box; background-color: transparent; background-image: none; background-origin: padding-box; background-position-x: 0%; background-position-y: 0%; background-repeat: repeat; background-size: auto; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-size-adjust: none; font-stretch: 100%; font-style: normal; font-variant: normal; font-weight: 400; line-height: normal; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;"></colgroup><tbody style="background-attachment: scroll; background-clip: border-box; background-color: transparent; background-image: none; background-origin: padding-box; background-position-x: 0%; background-position-y: 0%; background-repeat: repeat; background-size: auto; color: rgb(34, 34, 34); font-family: Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif; font-size: 13px; font-size-adjust: none; font-stretch: 100%; font-style: normal; font-variant: normal; font-weight: 400; line-height: normal; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;">[TR="bgcolor: transparent"]
[TD]
Agriculture[/TD]
[TD]Airway[/TD]
[TD]Digital Signage[/TD]
[TD]Smart City[/TD]
[TD]Smart Building[/TD]
[/TR]
[TR="bgcolor: transparent"]
[TD]-[/TD]
[TD="bgcolor: transparent"]-[/TD]
[TD="bgcolor: transparent"]-[/TD]
[TD="bgcolor: transparent"]Comp1[/TD]
[TD="bgcolor: transparent"]-[/TD]
[/TR]
[TR="bgcolor: transparent"]
[TD]-[/TD]
[TD="bgcolor: transparent"]-[/TD]
[TD="bgcolor: transparent"]Comp2[/TD]
[TD="bgcolor: transparent"]-[/TD]
[TD="bgcolor: transparent"]-[/TD]
[/TR]
[TR="bgcolor: transparent"]
[TD]Comp3[/TD]
[TD="bgcolor: transparent"]-[/TD]
[TD="bgcolor: transparent"]-[/TD]
[TD="bgcolor: transparent"]-[/TD]
[TD="bgcolor: transparent"]-[/TD]
[/TR]
[TR="bgcolor: transparent"]
[TD]-[/TD]
[TD="bgcolor: transparent"]-[/TD]
[TD="bgcolor: transparent"]-[/TD]
[TD="bgcolor: transparent"]-[/TD]
[TD="bgcolor: transparent"]-[/TD]
[/TR]
[TR="bgcolor: transparent"]
[TD]-[/TD]
[TD="bgcolor: transparent"]-[/TD]
[TD="bgcolor: transparent"]-[/TD]
[TD="bgcolor: transparent"]Comp5[/TD]
[TD="bgcolor: transparent"]Comp5[/TD]
[/TR]
</tbody>[/TABLE]

​

Question 3: ANSWER

Answer: Just add it to the list. See expected output above.​

 

[Aladin Akyurek]

Book1
	A	B	C	D	E
1	Agriculture	Airway	Digital Signage	Smart City	Smart Building
2	-	-	-	Comp1	-
3	-	-	Comp2	-	-
4	Comp3	-	-	-	-
5	-	-	-	-	-
6	-	-	-	Comp5	Comp5
7	-	-	-	-	-
8
Sheet1

In A2 enter, copy across, and down:

=IF(ROWS(A$2:A2)>ROWS(Table1),"",IF(ISNUMBER(MATCH(A$1,INDEX(Table1[[V1]:[V4]],ROWS(A$2:A2),0),0)),INDEX(Table1,ROWS(A$2:A2),1),"-"))

 

[fc_mojo]

	A	B	C	D	E
Agriculture	Airway	Digital Signage	Smart City	Smart Building
-	-	-	Comp1	-
-	-	Comp2	-	-
Comp3	-	-	-	-
-	-	-	-	-
-	-	-	Comp5	Comp5
-	-	-	-	-

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

</tbody>

Sheet1​

In A2 enter, copy across, and down:

=IF(ROWS(A$2:A2)>ROWS(Table1),"",IF(ISNUMBER(MATCH(A$1,INDEX(Table1[[V1]:[V4]],ROWS(A$2:A2),0),0)),INDEX(Table1,ROWS(A$2:A2),1),"-"))

Thank you, Thank You, Thank you....That worked like a charm!!

 

[Aladin Akyurek]

Thank you, Thank You, Thank you....That worked like a charm!!

You are welcome.