LinkBack URL
About LinkBacks
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
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
Hi shrooms,
Not sure but try this formula:
you can change the criteria by changing the letters in the formula that are in red to suit your need.
=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)))
Corine
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.Originally Posted by robert111
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
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)
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
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.
Last edited by shrooms; 12-12-2007 at 10:55 AM.
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks