need VLOOKUP to ignore blanks and carry on looking until it finds something

=VLOOKUP(A1,C:D,2,false)

The value in A1 appears several times in columns C. Sometimes D has a value, sometimes it's blank.

Can I make VLOOKUP keep on looking until it finds a value, and only return blank if they're all blank? Demo attached.

VLOOKUP will return the first value for the first match it hits within a column. That, in your example, is D1, since C1 is the first "John" it hits. Apart from altering the formula, adding in a last name for your name list could help improve your searches.

However, if you aren't stuck on using VLOOKUP, try this:

Please Login or Register  to view this content.

Needs to be confirmed with CTRL+SHIFT+ENTER

Hope this helps!

This MIGHT be what you want...

Formula:

Please Login or Register  to view this content.

It returns the value in Col D corresponding to the LAST match for John in Column C...

Originally Posted by pernekde

Hello,

How do I write the formula so that if the
lookup index or vlookup (cell) is blank, go to the next cell and use that value
if that one is also blank go to the next cell and use that value...?

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread