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!

 

[jeffreybrown]

Couldn't you just use this in column E...

=IF(C2=0,D2,C2)

 

[Sowelu]

Couldn't you just use this in column E...

=IF(C2=0,D2,C2)

If C2=0, I need C5 -- not D2.

This is a small example, my spreadsheet is huge and I will not know if one cell is zero where down the column is the NEXT value that would not be zero.

I am trying to stay away from manipulating spreadsheet -- unless there is a short way.
Because I foresee doing this over and over again every time I pull the data, I'd rather have a formula or VBA code (even though I am not too familiar with writing it) that will go through the data and pull out first non-zero results.

I appreciate the help.

 

[Domenic]

Assuming that F2 contains the number of interest, such as 345, try...

Confirmed with CONTROL+SHIFT+ENTER:

=INDEX($C$2:$C$7,MATCH(1,IF($A$2:$A$7=F2,IF($C$2:$C$7<>0,1)),0))

Hope this helps!

 

[Sowelu]

Assuming that F2 contains the number of interest, such as 345, try...

Confirmed with CONTROL+SHIFT+ENTER:

=INDEX($C$2:$C$7,MATCH(1,IF($A$2:$A$7=F2,IF($C$2:$C$7<>0,1)),0))

Hope this helps!

gives me an error... ((((

TEST_LOOKUP.xls
	A	B	C	D	E	F	G	H	I
1	Number	1	2	3
2	123	0.2	0	0.2		345	#N/A
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

 

[Domenic]

Did you confirm the formula with CONTROL+SHIFT+ENTER, not just ENTER?

 

[Sowelu]

Did you confirm the formula with CONTROL+SHIFT+ENTER, not just ENTER?

yes, I think error happens
because it finds this part
to be false

$C$2:$C$7<>0

it shows 0<>0.

I am not sure if this is the intention..
Thanks for helping me, Domenic

 

[Sowelu]

Did you confirm the formula with CONTROL+SHIFT+ENTER, not just ENTER?

oh, i think its because 345 has only zeroes there.
123 works and 234.

Let me try to apply it to my data... Thanks!

 

[Domenic]

oh, i think its because 345 has only zeroes there.

That's exactly it...

 

[Sowelu]

That's exactly it...

Domenic, you are the best!!!! Thanks so much!