Excel Magic Trick 1460: List All Relative Positions in a Single Cell using TEXTJOIN Array Formula

Learn how to look up an item in a list of items and list all the relative positions for that item in a single cell. See the functions TEXTJOIN, IF, ISNA, MATCH. See extensive discussion about Array Formulas. Learn different Formula Element options for listing all relative positions in a formula.

[drsteele]

TEXTJOIN never ceases to amaze! And neither does ExcelIsFun - great video, Mike. I've always thought that MATCH would be easier to use if it would deal with duplicates and return the relative position of each match, not just the first. Also, it would be better if it reported 0 instead of NA when there is no match.

[EricGiroux]

Hi Mike,

As usual, very informative Thank you very much! ;-)

Since I was in TABLE mood, I did your exercise with table nomenclature...so here's the result for those interested. :

IF(ISNA(MATCH(Table2[@[LOOKUP_VALUE]], Table1[LOOKUP_ARRAY], 0)), "LOOKUP_VALUE NOT IN LOOKUP_ARRAY", TEXTJOIN(", ", , IF(Table1[LOOKUP_ARRAY]=Table2[@[LOOKUP_VALUE]], ROW(Table1[LOOKUP_ARRAY])-ROW(Table1[[#Headers], [LOOKUP_ARRAY]]), "")))

Keep up the good, EXCELent, work! ;-)

[NicholasEricBallard]

This is perfect! Mike I've been trying to wrap this in an INDEX-MATCH function, where instead of returning "3, 4" - the row numbers - it returns "three, three" - the matching cell contents in the reference table. The problem is MATCH returns the first matching row in the list, so it's only returning the first matching value. Past videos you've made a function to start looking in the row after the first match, then the row after the second match, et cetera, for the MATCH function's lookup array.

Trying to combine INDEX-MATCH with TEXTJOIN would be so powerful! For my current project I am matching check numbers to purchase order numbers. There can be zero to many purchase order numbers for any one check number.

For example, Check # 1234 has 3 matching Purchase Orders in a table. Using TEXTJOIN, I need to Check # 1234, and return "PO 3495892, PO 3892358, PO 0144820".

Currently having to load the Excel worksheet into an IPython notebook and loop through each check number with boolean matching. Yikes! Is there any way to do this in Excel without resorting to VBA dark arts?

[hectorricardus]

I've searched this topic yesterday haha.. thanks!

[MohamedAlyCLAY]

cool trick in array formulas as always
thanks every time :)

[CMcAces]

Hi Mike,

I've just watched a few of your videos, they are amazing! I can't get over the enthusiasm and I'm so glad there are others I can relate to!

I really want to ask you a question about an excel project I'm working on although I'm sure I'll find some answers in your previous videos (I plan to watch them all)

I need to extract information from a .CSV file excluding certain useless columns, and copy the important data into another worksheet. The data needs to show two things, all information for records dated before the current month, then directly underneath I need to show all records dated with the current month. The amount of data will be different each month and I don't know how to tell excel how to do this.

Many thanks!
Chris McCabe

[DZDZ]

I don't think this video can be disliked, great ideas

[ikark]

Lovely tower-array formulas. :) It's shame that I can't use textjoin func in my Excel 2016 pro for so long..

[jcook]

Will they EVER offer these new functions to the non-subscription users?

[hamayounmughal]

What if we need to highlight particular cells with row value in conditional formatting?