Help with countif ... lookup....

[sillylupie77]

[HTML removed by admin]

 

[sillylupie77]

Pls help .... I am a real newbie !!

Thanks all ...

 

[ACommandLineKindaGuy]

Look at Help on VLOOKUP and pay specifica attentionthe the last optional parameter set to FALSE when the lis is not ascending

 

[barry houdini]

To return a result from the column to the left you need INDEX/MATCH not VLOOKUP. List doesn't need to be sorted if you use a zero as the 3rd argument of MATCH....so try

=INDEX(B3:B10,MATCH(C17,C3:C10,0))

 

[sillylupie77]

Thanks a bunch Barry! Solved my problem no. 1 !!!

Now I gotta understand the function ...

 

[barry houdini]

Can you explain problem 2 again, I'm not sure I quite understand

 

[sillylupie77]

Hi Barry,

Hehehe, never been good at expressing my problems ...

OK, what I wanted to achieve in calculating how many times the Inital PN # rollover ....

eg ... if I enter 7778 in C21

Initial PN # will appear as 1206 in C14 (thanks to you.....)

And ... the No. of Rollovers (where the problem is...) should be 1, instead of 2 ... reason being though 7778 appears twice it should be counted as only once....

Help pls, thanks...

 

[sillylupie77]

Did I do anything wrong? Why was my HTML removed?

If yes, I am sorry?....