#### Sowelu

##### New Member
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
ABCDEFGHI
1Number123
21230.200.2
32340.10.10
4345000
512300.220
6234000.15
71230.240.240.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.

THANKS!

### Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

#### jeffreybrown

##### Well-known Member
Couldn't you just use this in column E...

=IF(C2=0,D2,C2)

#### Sowelu

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

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

##### New Member
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
ABCDEFGHI
1Number123
21230.200.2345#N/A
32340.10.10
4345000
512300.220
6234000.15
71230.240.240.24
8
Sheet1

#### Domenic

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

#### Sowelu

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

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

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

That's exactly it...

#### Sowelu

##### New Member
That's exactly it...

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

Replies
3
Views
96
Replies
6
Views
135
Replies
6
Views
188
Replies
12
Views
229
Replies
4
Views
162

1,195,834
Messages
6,011,866
Members
441,651
Latest member
drewe2000

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