Lookup value in A, and return first non-null value in B

[RapidFireGT]

I'm looking for a formula to place in column C that will look for the value of A1 in column A, and then return the first non-null value in column B. Google seems to indicate that an INDEX/MATCH formula would be the way to go, but I can't seem to get it right as I admittedly haven't worked with INDEX/MATCH much in the past. Any help would be much appreciated!

Thanks in advance,

TABLE AS IT IS:

A	B	C
Joe
Joe	111
Joe
Matthew
Matthew
Matthew
Matthew	222
Matthew
Steve
Steve
Steve	333
Steve

<tbody>
</tbody>

DESIRED RESULT:

A	B	C
Joe		111
Joe	111	111
Joe		111
Matthew		222
Matthew		222
Matthew		222
Matthew	222	222
Matthew		222
Steve		333
Steve		333
Steve	333	333
Steve		333

<tbody>
</tbody>

 

[bosco_yip]

1] Assume table has header and data in A2:B13.

2] In C2, copied down :

=LOOKUP(9^9,$B$2:$B$100/($A$2:$A$100=A2)/($B$2:$B$100>0))

Regards
Bosco

 

[Tetra201]

@ bosco_yip:

Two things with your formula:
- it does not work for 0's in column B;
- it will return the last non-zero value for the match in column A.

Here is my take on it. This is an array formula (to be entered using Ctrl+Shift+Enter, not just Enter):

=INDEX($B$2:$B$100,MATCH(TRUE,IF($A$2:$A$100=A2,$B$2:$B$100&"","")<>"",0))

 

[Aladin Akyurek]

In C2 control+shift+enter, not just enter, and copy down:

=LOOKUP(9.99999999999999E+307,IF($A$2:$A$13=$A2,IF(ISNUMBER($B$2:$B$13),$B$2:$B$13)))