VLookup is not bring back values
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?
Answers
-
Can you provide a screenshot with sensitive/confidential data removed, blocked, and/or replaced with "dummy data" as needed?
-
Here you go:
Master Sheet
Data Entry Sheet
-
I don't see a "Name6" in your source data?
-
Focus on Name 6, here's a fresh snapshot as this is what's occurring.
Master Sheet:
Data Entry Sheet:
-
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)
-
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?
-
The INDEX/COLLECT should work. Give it a try and let me know.
-
It worked at first, then it refreshed itself and now the column says "Invalid Value" 😥
-
Can you provide a screenshot of the error and the exact formula?
-
Here's the screenshot:
=INDEX(COLLECT({Week 1 Range 3}, {Week 1 Range 3}, @cell <> "", {Name Range 6}, Name5), 1)
-
It looks like your column is "Names" and in your formula you have it listed as "Name".
-
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?
-
How are you adding it to week 2?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!