 SOLVED

Question releated to Match function

Hello Everyone,

In below screenshot, I am using the MATCH FUNCTION but apples, position shows 1

like - I want to show apple's position 1, 5, 7 (in comma seperated) with the help of MATCH FUNCTION

So, what formula should i write?

I am using Microsoft Office 2019 Home and student.

Here is a attached file..

7 Replies
best response confirmed by Zan_Hanifee (Regular Contributor)
Solution

Re: Question releated to Match function

@Zan_Hanifee MATCH isn't suitable for this, I'm afraid. Given your Excel version this might work though:

=TEXTJOIN(",",TRUE,IFERROR(1/(\$A\$2:\$A\$8=D4)*(ROW(\$A\$2:\$A\$8)-1),""))

Perhaps you need to enter the the formula with Ctrl-Shift-Enter (CSE) on your version. Not sure.

File attached.

Re: Question releated to Match function

Sir, Why we divide by 1?

Re: Question releated to Match function

Dividing into 1 generates errors for each mismatch which are then trapped and turned into blanks.  An alternative would be

= TEXTJOIN(",", TRUE, IF(fruit="apple", ROW(fruit)-1,""))

Excel 365 can look very different

= LET(
n, COUNTA(fruit),
k, SEQUENCE(n),
f, FILTER(k, fruit="apple"),
TEXTJOIN(",",,f))

Re: Question releated to Match function

Thank you so much sir.

Re: Question releated to Match function

Comparison type can be -1, 0, or 1. 1 searches for the largest value that is less than or equal to the search criterion.
Zero (0) searches for the first value that exactly matches the value search criterion. -1 searches for the smallest value that is greater than or equal to the search criterion.

Nikolino
I know I don't know anything (Socrates)

Re: Question releated to Match function

Thank you so much sir

Re: Question releated to Match function

I am not suggesting that anyone should do this; it is simply an exercise in the application of new methods under development within Excel 365.

= LAMBDA(lookup_value,lookup_array,
LET(
k, SEQUENCE(ROWS(lookup_array)),
AMATCHλ, LAMBDA(acc,k,
IF(INDEX(lookup_array,k)=lookup_value,
IF(LEN(acc), acc&","&k, k),
acc)),
REDUCE("", k, AMATCHλ))
)("apple", fruit)

Within the LET it defines the named sequence 'k' and a function that will append a single value from 'k' to 'acc' if the corresponding fruit matches the lookup value.  REDUCE then runs through each 'k' in turn.  The formula works as it is, but, if the enclosing Lambda function is named 'MULTIMATCHλ', it may be reused anywhere within the workbook, e.g.

= MULTIMATCHλ(UNIQUE(fruit), fruit)

will return any array of results corresponding to each fruit category.  A minor plus for the approach is that SEQUENCE is robust to the insertion of rows, whereas ROW is necessarily fragile.