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

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.

3 Answers

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.

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.

This is one of 'quirks' with the VLOOKUP. It will pull the first match it finds. In this case the very first "Steve" has a blank value so it is 0. The first Jane is also blank so it again returns 0 etc. There are a couple ways around this. you can simply sort the table that is referenced so the first matches are ones with values or you can write a different formula starting with cell J6.

=IF(L6<>"",K6,"")

after that change your VLOOKUP to the following

=VLOOKUP(C6,J:L,3,0)

You can also update your named range reference so you can use it but these columns will work fine for this formula. Doing it this way the VLOOKUP will only pull if there is a value in L6 and so on.

Let me know if this works for you.