Match Index Formula

NVRensburg

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

Attachments

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,635
Office Version
365
Platform
Windows
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

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

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

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

Board Regular
Joined
Jul 1, 2014
Messages
63
Office Version
365, 2016
Platform
Windows
I've used the web to upload the file so it can be downloaded if that helps?
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
43,407
Office Version
365
Platform
Windows
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
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
16SpencerPerson APerson APerson APerson A
17HargreavesPerson B, Person JPerson B, Person JPerson B, Person JPerson B, Person JPerson B
18Princes Street
19Mercy
20M OnePerson HPerson HPerson H
21Constellation Bus Station
22Sale MasonryPerson KPerson KPerson KPerson K
23Ryman Healthcare
16 MARCH 2020
Cell Formulas
RangeFormula
B16:G23B16=TEXTJOIN(", ",1,IF(B$3:B$14=$A16,$A$3:$A$14,""))
 

NVRensburg

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

NVRensburg

Board Regular
Joined
Jul 1, 2014
Messages
63
Office Version
365, 2016
Platform
Windows
I removed all the spaces, as you mentioned, but still #VALUE
 

Medhat

New Member
Joined
Aug 11, 2015
Messages
2
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
 

Forum statistics

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

This Week's Hot Topics

Top