finding the position of an element in a vector

[Guest]

I'd like to be able to specify a vector within a column, and then starting
at any location within that vector, find the first occurrence of a specific
value, starting at that location and searching upward. For example, I'd like
to create a function that would look like: FOOBAR("x", c10:c$2). Then if
the vector were actually
("","","y","","x","","","","x") FOOBAR should return a value of 4. (First
element is indexed "0". Thanks much for any help!

Trainrider

 

[Daniel.M]

Hi,

Look for the Match() worksheet function (index starts at 1)

=MATCH("x",("","","y","","x","","","","x") ,0) ' ===> 5

Help will give you more infos (binary search, sequential search, etc.)

Regards,

Daniel M.

 

[Guest]

Hi,

Look for the Match() worksheet function (index starts at 1)

=MATCH("x",("","","y","","x","","","","x") ,0) ' ===> 5

Thanks for your reply. Sorry I wasn't clear. If I have:

A
1
2
3
4 x
5 y
6
7 x
8

and I say MATCH("x",A1:A8) the function will return "4".
What I'm looking for is a new function, say REVMATCH which
will return "2". I want to start at the bottom and search upward.
Thanks again!
9

 

[Ron Rosenfeld]

Thanks for your reply. Sorry I wasn't clear. If I have:

A
1
2
3
4 x
5 y
6
7 x
8

and I say MATCH("x",A1:A8) the function will return "4".
What I'm looking for is a new function, say REVMATCH which
will return "2". I want to start at the bottom and search upward.
Thanks again!
9

Hmmm

Starting at the bottom and searching upward is the same as looking for the
highest numbered row (or last row) that contains "x".

That can be done with the formula:

=SUMPRODUCT(MAX(ROW(A1:A8)*(A1:A8="x")))

Hopefully you can figure out what to do with that result. I am confused
because in your first post you indicated a 0 based count starting at the first
element; and in this later post you indicate a 1 based count starting at the
last element. So depending on what you really want, you would subtract the
above from either 9 or 8.

e.g.

= n - SUMPRODUCT(MAX(ROW(A1:An)*(A1:An="x")))

or

= n+1 - SUMPRODUCT(MAX(ROW(A1:An)*(A1:An="x")))


--ron

 

[Ron Rosenfeld]

That works fine. Thank you!

You're welcome. Thanks for the followup

--ron