problems with MATCH worksheet function

[shrooms]

MATCH("b",{"a","b","c","b"},0) returns 2

how about if I would like to have the return result as 4 since I have another "b" within that same array?

Help me out with this problem thanks.

[robert111]

If I understand you correctly, with one "b" in the array, you want the returned answer to be 2, and for 2 "b"'s, 4.

So just multiply by 2

[corinereyes]

Hi shrooms,

Not sure but try this formula:

=MATCH("b",{"a","b","c","b"},0)+IF(SUM(IF({"a","b","c","b"}="b",1,0)) <= 1, SUM(IF({"a","b","c","b"}="b",1,0))-1, SUM(IF({"a","b","c","b"}="b",1,0)))

you can change the criteria by changing the letters in the formula that are in red to suit your need.

[shrooms]

If I understand you correctly, with one "b" in the array, you want the returned answer to be 2, and for 2 "b"'s, 4.

So just multiply by 2

ermm.. what I meant is that I would like the MATCH function to trace my second matched token which is "b" at the 4th position of the array instead of the first matched token that is at the 2nd position.

[daddylonglegs]

If the lookup value ["b"] is in A1 and range to search is D1:D4 then to find the position of the nth match

=SMALL(IF(D1:D4=A1,ROW(D1:D4)-ROW(D1)+1),n)

confirmed with CTRL+SHIFT+ENTER

[shrooms]

Because of the ctrl + shift + enter. im not able to apply that formulae into another formulae.. how may i bypass ctrl + shift + enter ?

=INDEX(B:B,SMALL(IF(B2:B22=B6,ROW(B2:B22)-ROW(B2)+1),VALUE(X2)),0)

[daddylonglegs]

how may i bypass ctrl + shift + enter ?

I'm not sure you can, easily.

Why can't you use CTRL+SHIFT+ENTER with the INDEX formula?

Perhaps explain what you want to achieve, exactly