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:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]111[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Matthew[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Matthew[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Matthew[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Matthew[/TD]
[TD]222[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Matthew[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD]333[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


DESIRED RESULT:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD][/TD]
[TD]111[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]111[/TD]
[TD]111[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD][/TD]
[TD]111[/TD]
[/TR]
[TR]
[TD]Matthew[/TD]
[TD][/TD]
[TD]222[/TD]
[/TR]
[TR]
[TD]Matthew[/TD]
[TD][/TD]
[TD]222[/TD]
[/TR]
[TR]
[TD]Matthew[/TD]
[TD][/TD]
[TD]222[/TD]
[/TR]
[TR]
[TD]Matthew[/TD]
[TD]222[/TD]
[TD]222[/TD]
[/TR]
[TR]
[TD]Matthew[/TD]
[TD][/TD]
[TD]222[/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD][/TD]
[TD]333[/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD][/TD]
[TD]333[/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD]333[/TD]
[TD]333[/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD][/TD]
[TD]333[/TD]
[/TR]
</tbody>[/TABLE]

 

[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)))