Match Index Formula

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Peter_SSs

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

Board Regular
Joined
Jul 1, 2014
Messages
63
Office Version
365, 2016
Platform
Windows
1584579647423.png

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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
43,501
Office Version
365
Platform
Windows
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
AB
1xa
2yb
3za
4
5ax, z
Sheet2
Cell Formulas
RangeFormula
B5B5=TEXTJOIN(", ",1,IF(B1:B3=A5,A1:A3,""))
 

NVRensburg

Board Regular
Joined
Jul 1, 2014
Messages
63
Office Version
365, 2016
Platform
Windows
I'm at a bit of a loss.

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

NVRensburg.xlsx
AB
1xa
2yb
3za
4
5ax, z
Sheet2
Cell Formulas
RangeFormula
B5B5=TEXTJOIN(", ",1,IF(B1:B3=A5,A1:A3,""))
1584581908143.png
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
43,501
Office Version
365
Platform
Windows
What if you select that formula cell (B5), press F2 then re-confirm the formula with Ctrl+Shift+Enter, not just Enter?
 

Peter_SSs

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

Board Regular
Joined
Jul 1, 2014
Messages
63
Office Version
365, 2016
Platform
Windows
I can't thank you enough for your help and patience with this! Thanks again so much
 

Forum statistics

Threads
1,089,528
Messages
5,408,807
Members
403,231
Latest member
FlorianPhil

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top