Lost with vlookup or matching?
I am needing to update a Smartsheet to reflect the date listed in a cell from another Smartsheet. Both sheets have an IP address that can be match on as there are no duplicates. The feeder sheet with the date has 210 entries and master sheet has 2800 entries. I want to copy the date field from the feeder sheet to the group field/cell on the master.
I tried the following formula in the group cell but it is unparseable:
=IF([IP]@row = 1 (true), VLOOKUP({feeder06262021 Range 1},[IP] @row, ="6/26")
Best Answer
-
Hi @ed.jaro
I hope you're well and safe!
I'd recommend using a combination of INDEX/MATCH instead.
Here's the structure.
=INDEX({ColumnWithTheValueYouWantToShow}; MATCH(CellThatHaveTheValueToMatch@row;
{ColumnWithTheValueToMatchAgainsTheCell}; 0))
Depending on your country/region, you'll need to exchange the comma to a period and the semi-colon to a comma.
Would that work/help?
I hope that helps!
Be safe and have a fantastic weekend!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Answers
-
Hi @ed.jaro
I hope you're well and safe!
I'd recommend using a combination of INDEX/MATCH instead.
Here's the structure.
=INDEX({ColumnWithTheValueYouWantToShow}; MATCH(CellThatHaveTheValueToMatch@row;
{ColumnWithTheValueToMatchAgainsTheCell}; 0))
Depending on your country/region, you'll need to exchange the comma to a period and the semi-colon to a comma.
Would that work/help?
I hope that helps!
Be safe and have a fantastic weekend!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Thank you for your quick and accurate response. I did end up changing the semi-colons to commas. And it works! But of course you knew that.
How would I or this there a way suppress the #NO MATCH results on the cells that are not listed in the feeder sheet? I'm think an IF statement is needed.
-
Excellent!
You're more than welcome!
You can use the IFERROR function right after the = to suppress the #NO MATCH message with something else or blank.
Make sense?
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Thank you Andree. I really like the links you post to backup your comments.
-
Happy to help!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!