ExpertLotto.com

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=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

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=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?

Lexus,

Yes that's just what I wanted

Thanks

RD

[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 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

=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