# A Formula to get First and the last value in a range

#### Arnolf

##### Board Regular
Hello,
I need to determine the first value and the last value in a range. The formula should return the name of the header.
I enclose an example. I need the formula for columns FIRST and LAST.
Thank you
Arnolf.
Book1.xls
ABCDEFGHI
2
3JanuaryFebruaryMarchAprilMayFIRSTLAST
401090FebruaryApril
500500MarchMarch
60704FebruaryMay
7903FebruaryApril
8
900000
1030005JanuaryMay
11
Sheet1

### Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Try:

In G4: =INDEX(\$A\$1:\$E\$1,1,MIN(IF(\$A2:\$E2>0,COLUMN(\$A2:\$E2))))
In H4: =INDEX(\$A\$1:\$E\$1,1,MAX(IF(\$A2:\$E2>0,COLUMN(\$A2:\$E2))))

Evaluated with CTRL+SHIFT+ENTER not just Enter

Then copied down.

Thank you NBVC,
I entered your formulas, but I cant get the results desired.
Is there somenthing I am doing wrong ?.
One more little request when there is no data or when the data is 0 in the range the formula should show blank cell.
Book1.xls
ABCDEFGHIJK
1
3JanuaryFebruaryMarchAprilMayFIRSTLASTFIRSTLAST
401090MarchMayFebruaryApril
500500AprilAprilMarchMarch
60704March#REF!FebruaryMay
7903MarchMayFebruaryApril
8JanuaryJanuary
900000JanuaryJanuary
1030005February#REF!JanuaryMay
11
Sheet1

Perhaps these, gives blank where there is no non-zero value in the range

In G4 copied down

=IF(SUM(B4:F4),INDEX(B\$3:F\$3,MATCH(TRUE,B4:F4>0,0)),"")

confirmed with CTRL+SHIFT+ENTER

in H4 copied down

=IF(SUM(B4:F4),LOOKUP(2,1/(B4:F4>0),B\$3:F\$3),"")

just normally entered

Try these, entered with CSE,

=INDEX(A1:M1,MATCH(TRUE,A2:M2<>0,0))

and for the last non-zero

=INDEX(A1:M1,MAX((A2:M2<>0)*COLUMN(A2:M2)))

also entered with CSE

Try...

G4, copied down:

=INDEX(\$B\$3:\$F\$3,MATCH(TRUE,INDEX(B4:F4>0,0),0))

H4, copied down:

=LOOKUP(2,1/INDEX(B4:F4>0,0),\$B\$3:\$F\$3)

Both these formulas need to be entered with just ENTER.

Hope this helps!

Arnolf said:
Thank you NBVC,
I entered your formulas, but I cant get the results desired.
Is there somenthing I am doing wrong ?.
One more little request when there is no data or when the data is 0 in the range the formula should show blank cell.

I figured out why they weren't working for you. The arrays need to start at column A, even though no data is entered.

If you adjust the left range to column A, both your requirements will be taken into account, (i.e. skips 0 and blanks).

Thanks.

Barry, Brian, Domenic, NBVC,
Great!!. Just perfect.
Gentlemen, thank you so much for your very kind solutions.
Cheers,
Arnolf.

Domenic said:
=INDEX(\$B\$3:\$F\$3,MATCH(TRUE,INDEX(B4:F4>0,0),0))

Domenic,

I haven't seen that one before, have to file it away for future use :wink:

barry houdini said:
Domenic said:
=INDEX(\$B\$3:\$F\$3,MATCH(TRUE,INDEX(B4:F4>0,0),0))

Domenic,

I haven't seen that one before, have to file it away for future use :wink:

Yeah, I like it too. I got this from Aladin.

Replies
10
Views
431
Replies
4
Views
103
Replies
3
Views
120
Replies
3
Views
90
Replies
1
Views
119

1,203,026
Messages
6,053,113
Members
444,639
Latest member
xRockox

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back