Sep 11 2021 09:03 PM
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.
Please help..??
Here is a attached file..
Sep 11 2021 09:26 PM
Solution@Excel 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.
Sep 11 2021 11:17 PM
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))
Sep 11 2021 11:32 PM
Sep 12 2021 03:20 AM
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.
Sep 11 2021 09:26 PM
Solution@Excel 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.