how to get VLookup to pick the second row when the first row is blank

[ESIERRA44]

I'm trying to get VLookup to pick the second row when the first row is blank. it's for time sheet purposes

example




i have tired VLookup, index match, xlookup. if someone could please help me solve this it would be greatly appreciated

[AliGW]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. As you are new, I have done it for you today.)

[AliGW]

Please update your forum profile with the current version you are using - 2019 or 365. Thanks.

There are instructions at the top of the page explaining how to attach your sample workbook.

[ESIERRA44]

this has been done

[AliGW]

I don't see a VLOOKUP anywhere!

You did not need to change the title again - as I said above, I had already done it for you.

Please update your forum profile with the current version you are using - 2019 or 365. Thanks.

[ESIERRA44]

I uploaded the doc. I'm trying to pull the daily start and stop times from the weekly schedule data tab to the schedule tab. if you notice Karon Adams has signed up for Monday, Tuesday, and weds. but it will only pull Monday. I need it to pull the next line where she signed up for Tuesday

[AliGW]

Please update your forum profile with the current version you are using - 2019 or 365. Thanks.

[ESIERRA44]

this has now been done

[Gregb11]

Why does Karon Adams have a separate row for each day but Kristin Adamson have only 1 row for each day.

[Gregb11]

Try this in cell E4 and copy down and across to the Start/Stop columns:

=SUMPRODUCT(('Weekly Schedule Data'!$C$2:$C$5=$A4)*(INDEX('Weekly Schedule Data'!$D$2:$Q$5,,MATCH(E$3,'Weekly Schedule Data'!$D$1:$Q$1,0))))

[ESIERRA44]

so how this works agents signs up for shifts on google docs. when they sign up it auto populates on to this sheet. so every time the sign up for a new shift it populates on another row. thats why i need it to ignore a field if its blank and go to the next one

[ESIERRA44]

nope didnt work

[AliGW]

That's a useless response, sorry. Explain in what way it didn't work:

Did it produce an error? If so, what error?
Did it produce the wrong results? If so, in what way?

Attach a sample workbook showing your attempt at using the formula.

Are you actually using GoogleSheets for this, or Excel?

[ESIERRA44]

you sound like you know what you are doing. i can email the doc and you email it back if it helps. im new to this forum so im not sure if you can download and edit the doc i posted

[AliGW]

No, sorry - we don't take things away from the forum. I will not be sharing my E-mail address with you or anyone else.

First, answer ALL three questions in my previous post, none of which you have addressed in your latest response.

Help us to help you!

[JohnTopley]

The formula in Post #10 from Gregb11 does work.

[ESIERRA44]

Hey, thanks for all of your help. It does work. the next issue now is when i copy the formula into other fields it fills with 12:00:00 AM. it needs to remain blank if no results are found.

[Gregb11]

It really depends on formatting. I took the file you initially uploaded and updated it with my formula in rows 8 and 9. You'll notice the cells are blank if no time is entered.
One caveat - will anybody actually had a start/end time of 12:00:00 AM? If so, it will not show up because I believe it's the same as 0.

[ESIERRA44]

awesome thanks this now works well. now for some reason when i upload it to google docs the schedule will no longer update the array formula from the weekly schedule data sheet

[ESIERRA44]

actually nevermind i figured it out. thanks everyone for all of your help