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

excelexcel-formulavlookup

I am having a problem with my vlookup.

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

VLOOKUP PROBLEM IM HAVING

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

Expected Result

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.

Best Answer

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

  1. Cell in column L > 0

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

Related Topic