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.

Company	Agriculture	Airway	Smart Building	Smart City
Comp1	=INDEX(Table1[Partner],MATCH($A$1,Table1[Partner],0),SEARCH(B1,Table1[[V1]:[V4]],0))
Comp2
Comp3
Comp4

<tbody>
</tbody>

Table1:

Partner	V1	V2	V3	V4
Comp1	Smart City	Internet of Things
Comp2	Digital Signage	Gaming and Entertainment
Comp3	Agriculture	Automotive and Transportation
Comp4	Industrial Automation and Energy	Internet of Things
Comp5	Retail	Retail Analytics	Smart City	Smart Building
Comp6	Industrial Automation and Energy	Internet of Things

<tbody>
</tbody>

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:
​

Agriculture	Airway	Digital Signage	Smart City	Smart Building
-	-	-	Comp1	-
-	-	Comp2	-	-
Comp3	-	-	-	-
-	-	-	-	-
-	-	-	Comp5	Comp5

<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;">
</tbody>

​

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
1	Agriculture	Airway	Digital Signage	Smart City	Smart Building
2	-	-	-	Comp1	-
3	-	-	Comp2	-	-
4	Comp3	-	-	-	-
5	-	-	-	-	-
6	-	-	-	Comp5	Comp5
7	-	-	-	-	-
8

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</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.