CODEWITHSUNDEEP
excel
Rose
Rose

Reputation: 203

VLOOKUP if 0, find next value

I have a list like this:

Column1, Column2
c,0
c,5
c,0
a,0
a,1
a,0
b,1
b,0
b,0

And I want to get the non-zero values for a,b and c. When doing VLOOKUP you get the first value.

i would like to get something like this:

a,1
b,1
c,5

Upvotes: 0

Views: 4746

Answers (2)

zipa
zipa

Reputation: 27869

If your data is in range A1:B9, and unique values in C1:C3 use this array formula in column D:

=VLOOKUP(C1,IF($B$1:$B$9>0,$A$1:$B$9),2,FALSE)

Please take note that array formulas are applied with Ctrl + Shift + Enter.

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152505

Use Index with Aggregate to return the position:

=INDEX(B:B,AGGREGATE(15,6,ROW($B$2:$B$10)/(($A$2:$A$10=D2)*($B$2:$B$10>0)),1))

enter image description here

Upvotes: 1

Related Questions