VLOOKUP with multiple hits

[SamueXCEl]

Hi - I'm looking to do a VLOOKUP that will pull the hit that is not blank.

In column C I have the name I will lookup, and in column N the numbers I want.

e.g.
Dog might be listed 10 times, but if the 1st line of Dog is a blank cell, how do I let VLOOKUP know to move to the 2nd line; or if the 2nd Dog is also blank, the 3rd line; etc.

Thanks

 

[Joe4]

VLOOKUP cannot do that. It will only return the values from the first match it finds.
However, if you sort your data, first on the field that you are matching ("Dog"), and then by the field you want to return in DESCENDING order (so the blanks fall to the bottom of the list), then your VLOOKUP should work.

 

[SamueXCEl]

Hi Joe - thanks, but the VLOOKUP is on a tab, that pulls data from another one. It is a summary of the source, my intention is for it to be a standalone tab.

I don't really want the source tab to be data sorted, because each time data is added (more cats or dogs), I'll have to change one tab for another - and then revert back for adding animals

 

[Joe4]

OK, you should be able to use the method described here: https://exceljet.net/formula/index-and-match-with-multiple-criteria
The first criteria is the value you are looking up.
The second criteria is the column you want to return is not blank.

So, let say that you have two columns, A and B, where you are matching on column A, and returning the value from column B, but want to return the first non-blank match in column B.
If the value you are looking up is in cell D1, then the formula would look something like this:

{=INDEX(B1:B500,MATCH(1,(A1:A500=D1)*(B1:B500<>"")))}

note that the squiggly brackets indicate that the formula must be entered with CTRL+SHIFT+ENTER, and not just ENTER (as described in that link).