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]

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

Oh, ok I'm learning

 

[Peter_SSs]

Thanks. I'm not sure what is going wrong. When I copy post #20 mini-sheet to a blank worksheet it works.
TEXTJOIN Help says it will return #VALUE! "If the resulting string exceeds 32767 characters" which clearly is not the case here.

What happens if you put this formula in a blank cell?

=TEXTJOIN(",",1,"a","b")

 

[NVRensburg]

Thanks. I'm not sure what is going wrong. When I copy post #20 mini-sheet to a blank worksheet it works.
TEXTJOIN Help says it will return #VALUE! "If the resulting string exceeds 32767 characters" which clearly is not the case here.

What happens if you put this formula in a blank cell?

=TEXTJOIN(",",1,"a","b")

Seems to work fine for that?

 

[Peter_SSs]

Seems to work fine for that?

I'm at a bit of a loss.

On another fresh sheet, manually make these entries (don't copy my mini-sheet)

NVRensburg.xlsx
	A	B
1	x	a
2	y	b
3	z	a
4
5	a	x, z
Sheet2

Cell Formulas
Range	Formula
B5	B5	=TEXTJOIN(", ",1,IF(B1:B3=A5,A1:A3,""))

 

[NVRensburg]

I'm at a bit of a loss.

On another fresh sheet, manually make these entries (don't copy my mini-sheet)

NVRensburg.xlsx
	A	B
1	x	a
2	y	b
3	z	a
4
5	a	x, z
Sheet2

Cell Formulas
Range	Formula
B5	B5	=TEXTJOIN(", ",1,IF(B1:B3=A5,A1:A3,""))

 

[NVRensburg]

 

[Peter_SSs]

What if you select that formula cell (B5), press F2 then re-confirm the formula with Ctrl+Shift+Enter, not just Enter?

 

[NVRensburg]

What if you select that formula cell (B5), press F2 then re-confirm the formula with Ctrl+Shift+Enter, not just Enter?

That works!!!!!!!!!!!!!!! Is there a reason for that?

 

[Peter_SSs]

That works!!!!!!!!!!!!!!!

Good news at last!

Is there a reason for that?

The formula is an 'array formula' since B1:B3 and A1:A3 in the formula both return an array of values, not just a single value. However, my understanding was that if you had a current version of Office 365 then the C+S+E confirmation was not required, and it certainly is not required when I enter the formulas.
It would be required if you were using your Excel 2016 version.

In your 365 version it might be worth going in to your Account Settings (where you got the image for post #18) and clicking the drop-down under 'Update Options' and choosing 'Update Now' to ensure that you do have the latest version for your subscription.

 

[NVRensburg]

I can't thank you enough for your help and patience with this! Thanks again so much