Acquire 1st Two Non-Blank & Non-Zero Numbers in Row

[Braunschweiger]

Is there a simple Excel function that will permit me to acquire the first 2 non-blank, non-zero numbers in a row? In the picture below, I'd like 24 to appear in 1st Score and 6 to appear in 2nd Score. As always, thanks in advance for your help!

[/URL][/IMG]

 

[JoeMo]

Assuming Scores are always positive values, try the LARGE function.

 

[Braunschweiger]

Assuming Scores are always positive values, try the LARGE function.

OUTSTANDING Sir! Thank you VERY much...that worked. I've never used the "LARGE" function before. Thanks again! Dave

 

[Braunschweiger]

Wait...the LARGE function will return the 1st and 2nd "largest" numbers...not necessarily the 1st 2 consecutive numbers.

 

[Braunschweiger]

Well, I did find a solution: Go To Special, Blanks, Delete. It's slow but it works. It will give me consecutive numbers. If anyone knows of a better way...please let me know.

Thanks!

 

[JoeMo]

Wait...the LARGE function will return the 1st and 2nd "largest" numbers...not necessarily the 1st 2 consecutive numbers.

Your OP doesn't say anything about "consecutive numbers". Can you explain exactly what you want? It would be helpful if you could post a couple of examples showing the raw data and the answer you want a formula to produce.

 

[Sam_D_Ben]

Please see if this helps you.

Excel 2013/2016
	E	F	G	H	I	J	K
3	Score1	Score2		2019	2018	2017	2016
4	6	24		6			24
5	12	5			12	5
6	1	6		1	6
7	2	4			2		4
Sheet1

Cell Formulas
Range	Formula
E4	{=INDEX($H4:$K4,SMALL(IF($H4:$K4<>"",COLUMN($H4:$K4)-COLUMN($H4)+1),1))}
F4	{=INDEX($H4:$K4,SMALL(IF($H4:$K4<>"",COLUMN($H4:$K4)-COLUMN($H4)+1),2))}
Press CTRL+SHIFT+ENTER to enter array formulas.