# Date corresponding to last value

#### hhenrion

##### Board Regular
Hi experts,

In range A1:A100 I have dates
In range B1:B100 I have values (a lot of them are zeros).
In range C1:C100 I have values (a lot of them are zeros).
In range D1:D100 I have values (a lot of them are zeros).

I try to get in cell B102 the date corresponding to the last non-zero value in range B1:B100 ...
Same for cell C102 with range C1:C100.
Same for D
...

Can you help ?

Rgds,

H.

### Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Hi H

Try:

=LOOKUP(2,1/(B1:B100),A1:A100)

Best regards

Richard

Hi Richard

If I'm not mistaken the 2 in your formula means that the number must be >= .5? I think it should be the number that Aladin uses to write, 9.9999999999999e307.

Kind regards
PGC

Thanks & First non-zero value

Richard,

thanks a lot.

And to get the date corresponding to the first non-zero value ?

Rgds,

H.

=INDEX(\$A\$1:\$A\$100,MATCH(TRUE,B\$1:B\$100>0,0))

Confirmed with Ctrl+Shift+Enter

HTH

Hi Kris

maybe <>0 instead of >0?. hhenrion doesn't tells us what kind of data it is. If it is financial data you could have negative values?

Cheers
PGC

If the values in the range are all either zero or positive then this seems to work (no CSE formula involved):

=INDEX(A1:A100,MATCH(0,B1:B100)+1)

Richard

Hi PGC

The 2 seems to work fine for me - is it not for you?

Richard

PS I'm pretty sure I got it from an Aladin/Barry Houdini post anyway

=INDEX(A1:A100,MATCH(0,B1:B100)+1)

I don't think it should work.

It would fails if there are consecutive 0s even if you change

MATCH(0,B1:B100,0)+1

Hi again Richard

The 2 seems to work fine for me - is it not for you?

No. I just tried your formula in a small range

=LOOKUP(2,1/(B1:B6),A1:A6)

I used in B1:B6 (1,2,3,0.1,0.2,0). The result is the value in A3 instead of the value in A5.

It makes sense, since you are comparing 2 to 1/B1:B6 and 1/0.1=10, 1/.2=5 are both bigger than 2 and so the lookup does not consider them.

Since you are using 2, the boundary value is 1/2=.5

That's why to catch all the numbers the formula should have instead of the 2 the biggest excel possible number.

Hope this makes sense.
PGC

Replies
4
Views
52
Replies
10
Views
255
Replies
1
Views
166
Replies
0
Views
203
Replies
1
Views
176

1,214,887
Messages
6,122,095
Members
449,064
Latest member
Danger_SF

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