Formula to find first value that is NON-ZERO with a VLOOKUP

[nwd9s]

Hello-

I am looking for help with a formula in the attached sample data spreadsheet. To describe it, basically I have a data set with a bunch of order numbers in column A and their values going out by date in columns B through whatever depending on the date. In the other sheet I have a list of Order numbers and am trying to write a formula to return the first value for that order that is not zero.

Here is my current formula, which does not work.

Please Login or Register  to view this content.

Unfortunatley, the way the data is maintained, I cannot get the list of orders in the WORKSHEET tab to be in the same order as the DATA tab, so a formula around this problem would be a great help.

Many thanks in advance for your help!!!

[NBVC]

Try:

=INDEX(INDEX(Data!$B$2:$L$18,MATCH(C5,Data!$A$2:$A$18,0),0),MATCH(TRUE,INDEX(INDEX(Data!$B$2:$L$18,MATCH(C5,Data!$A$2:$A$18,0),0)>0,0),0))  

copied down

[nwd9s]

Thank you, this works great!

[bejay60]

I tried to use this formula with a single list of data values but I get an error. Any idea what I need to change to get it to work with a one dimensional list. The formula which solved the original problem is:

=INDEX(INDEX(Data!$B$2:$L$18,MATCH(C5,Data!$A$2:$A$18,0),0),MATCH(TRUE,INDEX(INDEX(Data!$B$2:$L$18,MATCH(C5,Data!$A$2:$A$18,0),0)>0,0),0))

I changed the range $B$2:$L$18 to $B$2:$B$18 but it gives the value error.

[moconn]

Hi,

Can anyone help me modify this example to search vertically instead of horizontally.

I've tried adapting the formula in the working answer but it returns the correct answer for all bar the one i need it to!

I've attached my adapted workbook.

Thanks in advance..

[oeldere]

Data!c2 =IF(B2=0,"",A2&"yes") and drag down.

Worksheet!E5 =INDEX(Data!$A$1:$C$18,MATCH(Worksheet!C5&"yes",Data!$C$1:$C$18,0),2) and drag down.

See the attached file.

[FDibbins]

Hi,

Can anyone help me modify this example to search vertically instead of horizontally.

I've tried adapting the formula in the working answer but it returns the correct answer for all bar the one i need it to!

I've attached my adapted workbook.

Thanks in advance..

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread