Vlookup for first value <> ZERO

**ckatzman** · 10-26-2012, 02:16 PM

I am trying to use a vlookup to fish through a column of data and find the first cell with a value <>0. In column B, I have a list of numbers; in column D a list of dates (in ascending order). I want to return the date that corresponds with the first value in column B. Maybe Vlookup isn't necessarily the best function to use... Would this be easier with index or match?

**BeachRock** · 10-26-2012, 02:36 PM

Can you upload an example workbook?

**ckatzman** · 10-26-2012, 02:55 PM

Here is a copy of the data I'm working with.

**BeachRock** · 10-26-2012, 03:14 PM

Hi ckatzman,

I'm not sure if I understand what you need. Are you saying you want one cell to have a formula that will automatically display the first date that corresponds to the first entry in column B that is <>0?

Can you elaborate more about how you envision this to be in the outcome?

**ckatzman** · 10-26-2012, 03:20 PM

I am looking for the date that corresponds with the first non-zero value in column B, i.e. B72 is the first non-zero value in this particular data set; the date that occurs on is Dec-04 in column D. Is there a way to have a formula find a value that is not equal zero in column B and return the date in column D?

**icestationzbra** · 10-26-2012, 03:32 PM

see if this helps:

Please Login or Register  to view this content.

you can change the parameters in highlighted portion to derive different results (such as for >0, =0 or <0 etc.)...

**BeachRock** · 10-26-2012, 03:57 PM

Sorry katz, I still don't understand.

You already have the dates in column D aligned with the numbers in column B.... Where do those dates come from, another sheet? Something is missing from this equation...

**franunzio** · 10-26-2012, 04:08 PM

Hi Ckatzaman,
if i understood rightly you just need to find the first non zero value based on sorted date...if yes try this
create an incremental flag that assume value = 1 as soon as it is in correspondence of a non zero value, from that moment onward will increase by one.
Than by using index and match you can find the date associated to the flag 1. ( in this way you can find the first the second etc)
Flag:

Assuming that your set of date starts at row 5, in cell M5 insert the formula: =IF(AND(OR(B4=0,ISTEXT(B4)),B5=0),IF(ISERROR(0+M4),0,(0+M4)),IF(ISERROR(M4+1),1,(M4+1)))
To find the date:

insert the following formula in any cell in the worksheet:=INDEX(D5:D10,MATCH(1,M5:M10,FALSE),1) "I ASSUME THAT THE DATA RANGE IS BETWEEN B5 AND B10".

I ALSO ADDED SOME ERROR HANDLER IF YOU WANT TO ADD THE HEAD FOR THE FLAGS.

I hope it helps