Question releated to Match function

[Excel]

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..

[#2 · Accepted 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.

[#3]

Sir, Why we divide by 1?

[#4]

@Excel 

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))

[#5]

Thank you so much sir.

[#6]

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.

Hope I was able to help you with this info.

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

[#7]

Thank you so much sir

[#8]

@Excel 

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.