Match Index Formula

NVRensburg

Board Regular
Joined
Jul 1, 2014
Messages
63
Office Version
365, 2016
Platform
Windows
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?
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
43,407
Office Version
365
Platform
Windows
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 ..
1584516930473.png
.. on my post #7 mini-sheet then select cell A1 on that fresh worksheet and then Paste, what happens with he results?
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
43,407
Office Version
365
Platform
Windows
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

Board Regular
Joined
Jul 1, 2014
Messages
63
Office Version
365, 2016
Platform
Windows
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

Board Regular
Joined
Jul 1, 2014
Messages
63
Office Version
365, 2016
Platform
Windows
I hope I've done it right above?
This is the version I'm using
1584576989917.png
 

NVRensburg

Board Regular
Joined
Jul 1, 2014
Messages
63
Office Version
365, 2016
Platform
Windows
SAMPLE.xlsx
ABCDEFG
1WEEK STARTING16/03/202017/03/202018/03/202019/03/202020/03/202021/03/2020
2NAMEMONDAYTUESDAYWEDNESDAYTHURSDAYFRIDAYSATURDAY
3Person ASpencerSpencerSpencerSpencerTraining
4Person BHargreavesHargreavesHargreavesHargreavesHargreaves
5Person CTropicanaTropicanaTropicanaTropicanaTropicanaTropicana
6Person DDNWDNWDNWShore JunctionNo workDNW
7Person EHouse 1House 2House 3DNWDNWDNW
8Person FTropicanaTropicanaTropicanaTropicanaTraining
9Person GConstellationConstellation
10Person HM OneM OneM OneConstellationShore Junction
11Person I
12Person JHargreavesHargreavesHargreavesHargreavesHughes and Tuke
13Person KSale MasonrySale MasonrySale MasonrySale MasonryTraining
14Person LCMPCMPCMPCMPCMP
15SITEMONDAYTUESDAYWEDNESDAYTHURSDAYFRIDAYSATURDAY
16Spencer #VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!
17Hargreaves #VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!
18Princes Street #VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!
19Mercy #VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!
20M One #VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!
21Constellation Bus Station #VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!
22Sale Masonry #VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!
23Ryman Healthcare #VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!
16 MARCH 2020
Cell Formulas
RangeFormula
B16:G23B16=TEXTJOIN(", ",1,IF(B$3:B$14=$A16,$A$3:$A$14,""))
 

Forum statistics

Threads
1,089,220
Messages
5,406,929
Members
403,113
Latest member
ms_excel_recal_or_die

This Week's Hot Topics

Top