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.

 

[NVRensburg]

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

Thanks Mede, I tried that, and now it's giving me no value in cell B16, but it should be showing Person A?

 

[Peter_SSs]

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

If you start a fresh worksheet then click this icon ..

.. on my post #7 mini-sheet then select cell A1 on that fresh worksheet and then Paste, what happens with he results?

 

[NVRensburg]

If you start a fresh worksheet then click this icon .. View attachment 9217 .. on my post #7 mini-sheet then select cell A1 on that fresh worksheet and then Paste, what happens with he results?

I don't see that icon on my excel? I'll post a picture

 

[Peter_SSs]

I don't see that icon on my excel?

The icon is not in Excel, it is in post #7 as stated

click this icon .. .. on my post #7 mini-sheet

Here it is again (bigger)

 

[NVRensburg]

The icon is not in Excel, it is in post #7 as stated
Here it is again (bigger)

View attachment 9266

OH my bad, sorry about that.
Ok so here's the results if I do that

 

[Peter_SSs]

I think I misread your Office versions as 365 and 2019, not 2016 when I said you would have TEXTJOIN but I don't think #VALUE should be the result anyway, but what version were you using when you get those errors?

Can you post that sheet again (but all the way from cell A1 to G23) using XL2BB?

 

[NVRensburg]

I think I misread your Office versions as 365 and 2019, not 2016 when I said you would have TEXTJOIN but I don't think #VALUE should be the result anyway, but what version were you using when you get those errors?

Can you post that sheet again (but all the way from cell A1 to G23) using XL2BB?

SAMPLE.xlsx
	K
9
16 MARCH 2020

 

[NVRensburg]

I hope I've done it right above?
This is the version I'm using

 

[Peter_SSs]

I hope I've done it right above?

No, you have to select A1:G23 on the worksheet before clicking 'Capture Range'

 

[NVRensburg]

SAMPLE.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	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!
17	Hargreaves	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!
18	Princes Street	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!
19	Mercy	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!
20	M One	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!
21	Constellation Bus Station	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!
22	Sale Masonry	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!
23	Ryman Healthcare	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!
16 MARCH 2020

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