VLOOKUP, If Zero Skip to Next Result

[Sowelu]

Hi,
I've searched on forums for a few hours but couldn't find a solution for this.

Below is the data I have.

Say I need to get values from Column C. VLOOKUP'ed values return the first value, but i need it to move on to the next result if the first one = 0.
so I will get

123 - not 0, but 0.22
234 - 0.1
345 - 0.

TEST_LOOKUP.xls
	A	B	C	D	E	F	G	H	I
1	Number	1	2	3
2	123	0.2	0	0.2
3	234	0.1	0.1	0
4	345	0	0	0
5	123	0	0.22	0
6	234	0	0	0.15
7	123	0.24	0.24	0.24
8
Sheet1

I have the sheet sorted by other fields like I need it to, so I can't change sorting.
Items are not in order as you see, so I can't find the row and move down one.

Please Help!!

THANKS!

 

[Domenic]

You're very welcome!

 

[Sowelu]

One last thing.. I will probably need it next time:
if I dont know which column it is going to be..
Like here I need the values from columns specified in G..
Previous example was all data from column 2 -- C.

what do i do for the ranges in function to match the column number?
I usually use Match function for this, but I am not sure how to incorporate it in this function...

TEST_LOOKUP.xls
	A	B	C	D	E	F	G	H	I
1	Number	1	2	3
2	123	0.2	0	0.2		123	2	0.22
3	234	0.1	0.1	0		123	1
4	345	0	0	0
5	123	0	0.22	0
6	234	0	0	0.15
7	123	0.24	0.24	0.24
8	345	0	0.77	0
9
Sheet1

 

[Domenic]

Try...

=INDEX($B$2:$D$8,MATCH(1,IF($A$2:$A$8=F2,IF(INDEX($B$2:$D$8,0,MATCH(G2,$B$1:$D$1,0))<>0,1)),0),MATCH(G2,$B$1:$D$1,0))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[Sowelu]

Try...

..
...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

Thanks!! That formula works! --Great!

I have looked at the data and found out that sometimes there are instances
of Numbers that have values in other columns but no values in column I am searching. Below is example with 345.

Is there a way to get -- if it does not find a value within given column, take the first value that<>0 in the first row found with that number. If first row is empty, check the second etc. Is that too complicated?..

TEST_LOOKUP.xls
	A	B	C	D	E	F	G	H	I	J
1	Number	1	2	3
2	123	0.2	0	0.2		123		0.22	lookinginC
3	234	0.1	0.1	0		123	3	0.2	lookingincolumnind.InG
4	345	0	0	0.79		345		#N/A	lookinginBbutNeedtoget0.79
5	123	0	0.22	0
6	234	0	0	0.15
7	123	0.24	0.24	0.24
8	345	0	0.77	0
9
Sheet1

 

[Domenic]

If I understand you correctly, you'd like to search Column A for the first occurrence of 345 and return the first non-zero value in the corresponding row. If one does not exist, you'd like to search for the next occurrence of 345 in Column A and return the first non-zero value in the corresponding row, and so on, until a non-zero value is found. If this is correct, try the following formula that needs to be confirmed with CONTROL+SHIFT+ENTER...

=INDEX($B$2:$D$8,MIN(IF($A$2:$A$8=F4,IF($B$2:$D$8>0,ROW($B$2:$D$8)-ROW($B$2)+1))),MATCH(TRUE,INDEX($B$2:$D$8,MIN(IF($A$2:$A$8=F4,IF($B$2:$D$8>0,ROW($B$2:$D$8)-ROW($B$2)+1))),0)>0,0))

Hope this helps!

 

[Sowelu]

If I understand you correctly, you'd like to search Column A for the first occurrence of 345 and return the first non-zero value in the corresponding row. If one does not exist, you'd like to search for the next occurrence of 345 in Column A and return the first non-zero value in the corresponding row, and so on, until a non-zero value is found. If this is correct, try the following formula that needs to be confirmed with CONTROL+SHIFT+ENTER...

Hope this helps!

Yes, this is correct. This formula combined in an if statement with the one above gives me the desired result. Thank you!
I faced another problem though: I am using this in a VBA code as an array formula, and it gives me a mistake when formula>255 characters.
Using the very first solution you gave me here, it works, but I can't do anything else with it because adding extra crosses the 255 mark.

Do you know any solutions to this?
I also have another thread -- Dynamic Path based on Cell Value
that's where I try to put in the formula you gave me with a dynamic path into a VBA code. Any thoughts on how to make it work?

Thank you!!!!

 

[Sowelu]

If I understand you correctly, you'd like to search Column A for the first occurrence of 345 and return the first non-zero value in the corresponding row. If one does not exist, you'd like to search for the next occurrence of 345 in Column A and return the first non-zero value in the corresponding row, and so on, until a non-zero value is found. If this is correct, try the following formula that needs to be confirmed with CONTROL+SHIFT+ENTER...


Hope this helps!

Domenic, is it possible to write a VBA code that would do same thing as this formula?

right now the formula after I apply it is huge:
it first looks in a specified column, if it doesnt find anything and results in error, it is using the last solution you gave me where it would check for other possible values in any column for this Number.

=IF(ISERROR(INDEX([1_Repl.xls]Sheet1!$E$1:$E$5000,MATCH(1,IF([1_Repl.xls]Sheet1!$A$1:$A$5000=$C2,IF([1_Repl.xls]Sheet1!$E$1:$E$5000<>0,1)),0))),INDEX([1_Repl.xls]Sheet1!$C$2:$S$4616,MIN(IF([1_Repl.xls]Sheet1!$A$2:$A$4616=C3,IF([1_Repl.xls]Sheet1!$C$2:$S$4616>0,ROW([1_Repl.xls]Sheet1!$C$2:$S$4616)-ROW([1_Repl.xls]Sheet1!$C$2)+1))),MATCH(TRUE,INDEX([1_Repl.xls]Sheet1!$C$2:$S$4616,MIN(IF([1_Repl.xls]Sheet1!$A$2:$A$4616=C3,IF([1_Repl.xls]Sheet1!$C$2:$S$4616>0,ROW([1_Repl.xls]Sheet1!$C$2:$S$4616)-ROW([1_Repl.xls]Sheet1!$C$2)+1))),0)>0,0)),INDEX([1_Repl.xls]Sheet1!$E$1:$E$5000,MATCH(1,IF([1_Repl.xls]Sheet1!$A$1:$A$5000=$C2,IF([1_Repl.xls]Sheet1!$E$1:$E$5000<>0,1)),0)))

Given also that I will have to make a path 1_Repl.xls dynamic based on the other column and I am doing everything in VBA -- which doesnt allow more than 255 characters in array formula... maybe I could do it other way?

Please help!!

 

[Domenic]

Use defined names to reduce the formula to less than 255 characters. Also, INDIRECT can be used for a dynamic path. So, for example, assuming that B3 contains the variable for the path, the formula will be entered in D3, and the values being returned are numerical values, try the following...

Select D3

Insert > Name > Define

Name: BigNum

Refers to:

=9.99999999999999E+307

Click Add

Name: RowNum

Refers to:

=MIN(IF(INDIRECT("'["&$B3&"_Repl.xls]Sheet1'!A2:A4616")=$C3,IF(INDIRECT("'["&$B3&"_Repl.xls]Sheet1'!C2:S4616")>0,ROW(INDIRECT("2:4616"))-2+1)))

Click Add

Name: Result1

Refers to:

=INDEX(INDIRECT("'["&$B3&"_Repl.xls]Sheet1'!E1:E5000"),MATCH(1,IF(INDIRECT("'["&$B3&"_Repl.xls]Sheet1'!A1:A5000")=$C2,IF(INDIRECT("'["&$B3&"_Repl.xls]Sheet1'!E1:E5000")<>0,1)),0))

Click Add

Name: Result2

Refers to:

=INDEX(INDIRECT("'["&$B3&"_Repl.xls]Sheet1'!C2:S4616"),RowNum,MATCH(TRUE,INDEX(INDIRECT("'["&$B3&"_Repl.xls]Sheet1'!C2:S4616"),RowNum,0)>0,0))

Click Ok

Then try...

D3:

=LOOKUP(BigNum,CHOOSE({1,2},Result2,Result1))

Hope this helps!