Index-Match formula to return the first non-blank match

Question

I am trying to write an index match formula to apply to in the following scenario:

 1  Column A       Column B        Column C
 2   Duck                           123ABC 
 3   Goose           .5             123ABC
 4   Duck                           456DEF
 5   Duck                           456DEF
 6   Goose           .9             456DEF

The formula formula will be placed in Column B only on blank lines by a VBA Macro. I am working from the following formula:

=INDEX($B$1:$B$6),MATCH(C2,$C$2:$C:6,0))

The idea is that the resulting data set would appear as below:

 1  Column A       Column B        Column C
 2   Duck            .5             123ABC 
 3   Goose           .5             123ABC
 4   Duck            .9             456DEF
 5   Duck            .9             456DEF
 6   Goose           .9             456DEF

In order to achieve this I can see two possible options:

1. Have criteria in the formula to not return a value if the lookup finds a "blank" cell to return. It would need to continue looking until it finds a value (a number).

2. Have a criteria that is part of the lookup which only returns a match if Column A says "Goose" (It will always be the case that the row that has a value has "Goose" in Column A.

I have researched online but can't find a method that works.

Answer 1

I was able figure this out on my own. The following array formula does what I was describing above:

=INDEX($B$1:$CB$6,MATCH(1,INDEX(($C$1:$C$6=C2)*($B$1:$B$6<>""),0),0))