Help with v lookup to skip blanks

[MarkDave]

Hi, losing my mind with this formula. In sheet one, I need a v lookup that looks at sheet 2, where there may be two options. My v lookup needs to ignore the blank one, then look again until it see the cell with text in and then return that value - any ideas?!

 

[Peter_SSs]

any ideas?

Yes, two

1. I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
   
   
2. MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
   
   Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[MarkDave]

Changed details; windows and o365 - I don’t even no where to start to be honest, tried everything and keep deleting, so nothing even worth sharing ?

 

[Joe4]

Hi, losing my mind with this formula. In sheet one, I need a v lookup that looks at sheet 2, where there may be two options. My v lookup needs to ignore the blank one, then look again until it see the cell with text in and then return that value - any ideas?!

VLOOKUP will always return the first instance it finds.

A few options:

1. Can you sort your lookup range, so that all the rows with blanks in that column are sorted to the bottom of your list? Then your VLOOKUP function will hit the matches with values first, and should work.

2. Are the values you trying to return numeric? If so, you might be able to use SUMIF instead, to sum up the two matches rows (+/blank plus any number will just return that number).

 

[Peter_SSs]

nothing even worth sharing

Some sample data and the expected results (entered manually) is what we are looking for, so we know what you are dealing with in terms of data, layout, expectations etc. otherwise we are taking a complete guess about what is in front of you. ?

 

[MarkDave]

Sorted it…thanks all

 

[Joe4]

Curious...how did you resolve it?

 

[Peter_SSs]

Glad to hear you got the solution.

Do you mind posting about your solution? Then it is perfectly fine to mark your post as the solution to help future readers. Otherwise, please do not mark a post that doesn't contain a solution.