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:

Please Login or Register  to view this content.

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

[shrooms]

I tried ctrl + shift + enter with the INDEX but it didnt work. It only worked if its =SMALL(IF(D1:D4=A1,ROW(D1:D4)-ROW(D1)+1),n) alone.

Basically, what im trying to achieve is to have X2 as the n value. It is the box where i input my nth figure so that it will match with the respective nth token. Then B6 is the text that I need to match.

Eg.
B2:a
B3:b
B4:c
B5:d
B6:b
B7:e
B8:f
.
.
.
X2:=2

Therefore, I tried
=INDEX(B:B,SMALL(IF(B2:B22=B6,ROW(B2:B22)-ROW(B2)+1),VALUE(X2))+ROW(A1),0)
with ctrl+shift+enter which didnt work. It only works if i separate =SMALL(IF(B2:B22=B6,ROW(B2:B22)-ROW(B2)+1),VALUE(X2)) into another box.

[jasoncw]

Using the Index formula will only return the same value as what is in cell B6, so I'm confused as to why you are using Index. Can you explain further?

Also, what do you mean by "it didn't work"? If you change Row(B2) to Row(B1) I think your formula will work.

Jason

[shrooms]

oh.
=INDEX(B:B,SMALL(IF(B2:B22=B6,ROW(B2:B22)-ROW(B2)+1),VALUE(X2))+ROW(A1),0)
will be needed so that when i drag it down all the tokens after "b" will be shown on my list.
for this case, it would be "e" and "f"

The part that didnt work is
=INDEX(B:B,SMALL(IF(B2:B22=B6,ROW(B2:B22)-ROW(B2)+1),VALUE(X2))+ROW(A1),0)
with ctrl + shift + enter as this is supposed to convert

SMALL(IF(B2:B22=B6,ROW(B2:B22)-ROW(B2)+1),VALUE(X2)) into 5

Therefore with
=INDEX(B:B,5+1,0)
that gives =INDEX(B:B,6,0)
it will return the result "b" [ since row(A1) is equals to the value 1 ]

[jasoncw]

oh.
=INDEX(B:B,SMALL(IF(B2:B22=B6,ROW(B2:B22)-ROW(B2)+1),VALUE(X2))+ROW(A1),0)
will be needed so that when i drag it down all the tokens after "b" will be shown on my list.
for this case, it would be "e" and "f"

Ah, that makes more sense now. Try the following, then copy down, and see if it works:

Please Login or Register  to view this content.

HTH

Jason

[shrooms]

Oh.. i guessed it worked. thanks alot. finally.

[NBVC]

You can't use whole column reference, B:B.... you must define a range (which starts at B2), like B2:B26.

[shrooms]

B:B works fine with me. anyway thanks alot.

[jasoncw]

No problem. Glad it helped.

Jason