VLookup is not bring back values

Gladys Liley

I am using vlookup in a master tracking sheet that is looking up Names and bring back date values in a data entry sheet. The data entry sheet is updated on a weekly basis from the same group of Names multiple times during the week so the formulas is suppose to bring back the dates activities are completed with rows added at the top:

Week 1: =VLOOKUP(Name#, {Source Sheet Range#}, 2, 0)

Week 2: =VLOOKUP(Name#, {Source Sheet Range#}, 3, 0)

Week 3: =VLOOKUP(Name#, {Source Sheet Range#}, 4, 0)

The formula is no longer returning dates, the value for week 1 and 2 are inconsistently blank. It appears that as the user enters data, the formula is bring back "blanks" for week 1 as it's reading the new line while populating week 2. In some cases both week 1 and week 2 are blank.

Any suggestions?

Paul Newcome

Can you provide a screenshot with sensitive/confidential data removed, blocked, and/or replaced with "dummy data" as needed?

Gladys Liley

Here you go:

Master Sheet

Data Entry Sheet

Paul Newcome

I don't see a "Name6" in your source data?

Gladys Liley

Focus on Name 6, here's a fresh snapshot as this is what's occurring.

Master Sheet:

Data Entry Sheet:

Paul Newcome

So the VLOOKUP is working correctly. It is finding the first row for Name 6 and pulling from the first column of that same row.

If you are trying to pull 07/22/20 from the second row, you are going to want to use a different formula.

=INDEX(COLLECT({Week 1 Column}, {Week 1 Column}, @cell <> "", {Name Column}, "Name 6"), 1)

Gladys Liley

I'd like to pull a date for each week, once a date is logged. However as week 2 is entered, week 1 and week 3 will be blank and farther down in the sheet is the date for week 1. Is this formula still ok?

Paul Newcome

The INDEX/COLLECT should work. Give it a try and let me know.

Gladys Liley

It worked at first, then it refreshed itself and now the column says "Invalid Value" 😥

Paul Newcome

Can you provide a screenshot of the error and the exact formula?

Gladys Liley

Here's the screenshot:

=INDEX(COLLECT({Week 1 Range 3}, {Week 1 Range 3}, @cell <> "", {Name Range 6}, Name5), 1)

Paul Newcome

It looks like your column is "Names" and in your formula you have it listed as "Name".

Gladys Liley

i'm still having issues with it. when i add the formula to week 2, it makes the week 1 values invalid. so i've kept the vlook up in week 2 and week 3 except the values are empty as previously displayed. Any thoughts?

Paul Newcome

How are you adding it to week 2?