Hi,
VLOOKUP() will always return the first match found so, if you search column A for "BP" then you will get "" returned.
If you want to return the first value where column A equals "BP" and column B is not empty, one formula option is:
Code:
=INDEX(B2:B7,MATCH(1,(A2:A7="BP")*(B2:B7<>""),0))
This is an array formula, so when you type it into the formula bar with CTRL+SHIFT+ENTER, not just ENTER.
If you want to return multiple results (say ALL results where column A equals "BP" and column B is not empty) then you should use the advanced filter instead of formulas.
Hope that helps...