Excel Help - Match Function

Excel Help - Match Function

Postby Red Devil » Tue Nov 24, 2009 12:31 am

Can anybody help me with a problem I have with the match function in Excel.

If you have a list of numbers in a column and you wanted to find the previous occurence of a cell's data you would use something like

=match(b2,b3:b100,0)

Does anybody know how to find the next previous occurrence in the list

Rgds

RD
Red Devil
 
Posts: 346
Joined: Tue Apr 25, 2006 3:26 pm

Postby Lexus » Tue Nov 24, 2009 6:05 am

[quote=Red Devil]
Can anybody help me with a problem I have with the match function in Excel.

If you have a list of numbers in a column and you wanted to find the previous occurence of a cell's data you would use something like

=match(b2,b3:b100,0)

Does anybody know how to find the next previous occurrence in the list

Rgds

RD
[/quote]

Greetings.

It that is necessary for you. It is necessary to alter from search in a line, on search in columns.
It is necessary to use DISPLACEMENT function
At me formulas in Russian, at you should be in English
You do not have the required permissions to view the files attached to this post.
Lexus
 
Posts: 42
Joined: Sun Aug 09, 2009 11:02 am

Postby Red Devil » Tue Nov 24, 2009 7:52 pm

Lexus,

Thanks for your reply I'm not sure I follow your sheet or if it answers what I'm after. I have attached a simple sheet to explain better.

Rgds

RD
You do not have the required permissions to view the files attached to this post.
Red Devil
 
Posts: 346
Joined: Tue Apr 25, 2006 3:26 pm

Postby Lexus » Tue Nov 24, 2009 11:18 pm

[quote=Red Devil]
Lexus,

Thanks for your reply I'm not sure I follow your sheet or if it answers what I'm after. I have attached a simple sheet to explain better.

Rgds

RD
[/quote]

It what You wanted?
You do not have the required permissions to view the files attached to this post.
Lexus
 
Posts: 42
Joined: Sun Aug 09, 2009 11:02 am

Postby Red Devil » Tue Nov 24, 2009 11:53 pm

Lexus,

Yes that's just what I wanted

Thanks

RD :-)
Red Devil
 
Posts: 346
Joined: Tue Apr 25, 2006 3:26 pm

Postby sysp34 » Wed Dec 09, 2009 12:22 am

[quote=Lexus:1259097490]
It what You wanted?
[/quote]

[quote=Red Devil:1259099580]
Lexus,
Yes that's just what I wanted

Thanks
RD :-)
[/quote]

nice n simple formula :cool: lexus btw is there any agile code for И так далее (иÑ￾пользуÑ￾ N+N1+N2+N3+N4+…) instead adding manually N+N1+N2+N3+N4+… :finger:

=MATCH(C$1,OFFSET($B$2,(C2+C3),0):$B$20000,0)

i think this is light weight calculation for skip but the challenge are adding manually N+N1+N2+N3+N4+…

well maybe we can use another formula, thank to Carbob who enlighten me about vertical/horizontal skip formula using an array formula :cool: :cool: :cool:

=IF(ROWS(C$2:C3)<=COUNTIF($B$2:$B$1448,C$1),SMALL(IF($B$2:$B$1448=C$1,ROW($B$2:$B$1448)-ROW(C$1)+1),ROWS(C$2:C3))-SUM(C$2:C2)-1,"")

note: after copy and paste the formula press CTRL + ALT + DEL

good luck
You do not have the required permissions to view the files attached to this post.
sysp34
 
Posts: 559
Joined: Thu Nov 30, 2006 2:22 am


Return to Anything Else

Who is online

Users browsing this forum: No registered users and 1 guest

cron