VLOOKUP Returns 0 even though there is a value in the line below the first result

Question

I am having a problem with my vlookup.

I have data that is in the following format: (see screenshot)

I ran my VLOOKUP formula as =VLOOKUP(C6:C11,named_range,2,FALSE)

"named_range" is what i used for my named range so that everything was an absolute reference. The named range is the entire selection on the right

I know that vlookup returns the value of the first result it finds, which is why "0" is returned for Steve, Ben, and Jane.

However I am trying to figure out how I can make it do the following:

If the vlookup finds a matching value that has a blank cell associated with it, look down the list until you find that matching value that has somthing in the cell next to it.

Here is the expected result that I would like (this is made manually of course):

I have done a ton of research but cannot find a way to solve this problem... I was leaning in the direction of MATCH and INDEX but nothing seemed to fit my requirements.

Thanks in advance hope I explained ok.

Answer 1

The simplest way I can think of to do this is to add a test to see if the cells are blank:

=VLOOKUP(C6,IF(ISBLANK($L$6:$L$18),0,$K$6:$L$18),2,FALSE)

This is an array formula, so it will need to be entered using Ctrl+Shift+Enter.

Answer 2

Another way to solve the problem is this:

{=INDEX(K6:L17,MATCH(1,(K6:K17=C6)*(L6:L17>0),0),2)}

This is also an array formula (so you'll need to use Ctrl+Shift+Enter).

The asterisk is the AND operator for array formulas (the OR operator would be the +). What it does then is that the MATCH formula is looking for the first row, where both conditions are TRUE, i.e. 1:

1. Cell in column K = C6 (Bob)

AND

2. Cell in column L > 0

You can find a very thorough explanation here: Index-Match-Combination.