Index matching multiple columns

[weeble33]

Hello,

This seems easy, or at least like it should've been answered, but I can't find it. I need a formula that looks at cell a1, searches for it in B:B, and returns the value in C:C. simple enough, but there are some caveats:
-B:B most likely contains multiple instances of the string in A1
-All but one of those instances above will have a number in C:C, and that's what I want to return.
-I need an array index formula because this will be taking the list in C:C and essentially shortening it

Ex.
fffg...0
ggh...0
ggh...42
ggh...0
sjkj...5

So if a1 contained ggh, I want D1 to be 42.

[Haseeb Avarakkan]

Hello,

try any of these with CTRL+SHIFT+ENTER,

=VLOOKUP(A1,IF(C2:C100>0,B2:C100),2,0)

=INDEX(C2:C100,MATCH(1,IF(B2:B100=A1,IF(C2:C100>0,1)),0))

[weeble33]

The vlookup gave me a #value error when putting it in. But the index seems to be working great! I don't know why I always forget about nested ifs...And here I was trying to get and() to handle arrays.

thank you