Same IFERROR | VLOOKUP formula works on some lines and not others
Hello,
I'm currently having issues with my VLOOKUP auto-populate formula, which is spread across multiple sheets.
(1) Formula works in another sheet;
(2) As shown below, I didn't receive an error message. However, it isn't pulling the information at all and instead inputs "-" or blank in each field.
Formula Used:
=IFERROR(VLOOKUP([Task Name]3, {Project Intake Sheet Range 3}, 7, false), "-")
Context:
Screenshot of sheet where formula lives. As you can see it isn't pulling the data from the other sheet and automatically inserts "-" or blank in fields.
Screenshot of sheet where data is being pulled from.
Answers
-
Hi @Kaye T.
How are you populating the formula down the rows? If you're using drag-fill, this will update your original reference of [Task Name]3 to be [Task Name]4, [Task Name]5, etc as you drag.
Try adding $ before the 3 to make sure it locks on to the 3rd cell:
=IFERROR(VLOOKUP([Task Name]$3, {Project Intake Sheet Range 3}, 7, false), "-")
Another thing to note is that it looks like you're bringing in two Date cells into a Text/Number field. In this instance you'll want to add "" to the end of the VLOOKUP so that it returns the date as text instead of a Date value (which will error).
=IFERROR(VLOOKUP([Task Name]$3, {Project Intake Sheet Range 3}, 7, false) + "", "-")
If this hasn't helped, remove the IFERROR from around your formula. It's returning "-" because there's an error, but I'd like to know which error you're getting (ex. #NO MATCH meaning it can't find the matching value of [Task Name]3 across sheets, or #INVALID COLUMN VALUE if it's trying to return a date, etc). See: Formula Error Messages
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Hi @Genevieve P. ,
The error message I receive is #INCORRECT ARGUMENT SET
Thank you for attaching the "Formula Error Messages Sheet" - I'll take a look at that now.
-
Hi @Kaye T.
That's really helpful! Is it possible that your range is incorrectly selected?
{Project Intake Sheet Range 3}
Try recreating this range to ensure all the columns you need are included to see if this helps.
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Hi @Genevieve P. ,
With the assistance of your resources, I was able to get the formula to work. Thanks so much for everything.
Have a great day! 😀
-
Wonderful, I'm glad to hear this! 🙂
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!