Match Index Formula

[NVRensburg]

Hi there
This is the table that I have populated, now in the bottom table I want to use formula's to find the match the information from the Rows and Columns to put the names in the table from above (if that makes any sense). Please could someone help me with the correct formula.

 

[sykes]

What's going to happen when two workers attend the same site, on the same day? For example Hargreaves on Monday 16th, has both Person B, and person J attending. What would you expect the result to be, in B17?

 

[Peter_SSs]

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version.

Also, can you give us the sample data again (with expected results) using XL2BB?
Most helpers are not so keen on manually typing out so much sample data to test their ideas.

 

[NVRensburg]

What's going to happen when two workers attend the same site, on the same day? For example Hargreaves on Monday 16th, has both Person B, and person J attending. What would you expect the result to be, in B17?

I'm not sure, what would you suggest?

 

[NVRensburg]

I'm not quite sure how to get the XL2BB to work. Can I not attach a sample sheet to this thread? I've also updated my profile as requested

 

[NVRensburg]

I've used the web to upload the file so it can be downloaded if that helps?

Download - TransferNow

Click to download the files for this transfer with TransferNow

www.transfernow.net

 

[Peter_SSs]

I'm not quite sure how to get the XL2BB to work.

If you follow the link I provided it details the steps to install and use XL2BB.

I've also updated my profile as requested

Thanks, that helps as it means you have the TEXTJOIN function. Therefore, this may be what you are after to work in both versions you have?
Formula copied across and down. First though you will need to remove the space characters from the end of all the names in A16:A23 so they match exactly the names in the top section.

NVRensburg.xlsx
	A	B	C	D	E	F	G
1	WEEK STARTING	16/03/2020	17/03/2020	18/03/2020	19/03/2020	20/03/2020	21/03/2020
2	NAME	MONDAY	TUESDAY	WEDNESDAY	THURSDAY	FRIDAY	SATURDAY
3	Person A	Spencer	Spencer	Spencer	Spencer	Training
4	Person B	Hargreaves	Hargreaves	Hargreaves	Hargreaves	Hargreaves
5	Person C	Tropicana	Tropicana	Tropicana	Tropicana	Tropicana	Tropicana
6	Person D	DNW	DNW	DNW	Shore Junction	No work	DNW
7	Person E	House 1	House 2	House 3	DNW	DNW	DNW
8	Person F	Tropicana	Tropicana	Tropicana	Tropicana	Training
9	Person G			Constellation	Constellation
10	Person H	M One	M One	M One	Constellation	Shore Junction
11	Person I
12	Person J	Hargreaves	Hargreaves	Hargreaves	Hargreaves	Hughes and Tuke
13	Person K	Sale Masonry	Sale Masonry	Sale Masonry	Sale Masonry	Training
14	Person L	CMP	CMP	CMP	CMP	CMP
15	SITE	MONDAY	TUESDAY	WEDNESDAY	THURSDAY	FRIDAY	SATURDAY
16	Spencer	Person A	Person A	Person A	Person A
17	Hargreaves	Person B, Person J	Person B, Person J	Person B, Person J	Person B, Person J	Person B
18	Princes Street
19	Mercy
20	M One	Person H	Person H	Person H
21	Constellation Bus Station
22	Sale Masonry	Person K	Person K	Person K	Person K
23	Ryman Healthcare
16 MARCH 2020

Cell Formulas
Range	Formula
B16:G23	B16	=TEXTJOIN(", ",1,IF(B$3:B$14=$A16,$A$3:$A$14,""))

 

[NVRensburg]

Thank you so much, that looks perfect....however I'm getting a #VALUE when I put those formula's in. Any idea's why?

 

[NVRensburg]

I removed all the spaces, as you mentioned, but still #VALUE

 

[Medhat]

To avoid #value use the below instead and copy it across in all range

=IFERROR(TEXTJOIN(", ",1,IF(B$3:B$14=$A16,$A$3:$A$14,"")),"")
Cheers
Mede