VLOOKUP Returns 0 even though there is a value in the line below the first result
<p>I am having a problem with my vlookup.</p>
<p>I have data that is in the following format: (see screenshot)</p>
<p><img src="https://i.stack.imgur.com/YDMVd.gif" alt="VLOOKUP PROBLEM IM HAVING" /></p>
<p>I ran my VLOOKUP formula as =VLOOKUP(C6:C11,named_range,2,FALSE)</p>
<p>&quot;named_range&quot; 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</p>
<p>I know that vlookup returns the value of the first result it finds, which is why &quot;0&quot; is returned for Steve, Ben, and Jane.</p>
<p>However I am trying to figure out how I can make it do the following:</p>
<p>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.</p>
<p>Here is the expected result that I would like (this is made manually of course):</p>
<p><img src="https://i.stack.imgur.com/ryjX8.gif" alt="Expected Result" /></p>
<p>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.</p>
<p>Thanks in advance hope I explained ok.</p>