How can I exclude "child rows" when executing VLOOKUP?
I am currently trying to update the VLOOKUP formula to exclude child rows. I setup a reference sheet to run the formula, but the only way for it to run properly is to add the child rows. If any child row value is changed, it shows as #NO MATCH, however, I would just like to appear blank. I am currently running the current formula:
=VLOOKUP([Compound/Product Name]@row, {Copy of Product Names - DO NOT MOVE OR DEL Range 1}, 2, 0)
Is there a syntax I can add to the current formula to bypass any child rows, or do I have to change the source reference sheet?
Answers
-
If you create a helper column on your source sheet for of
=COUNT(CHILDREN())
You can then use this with INDEX(COLLECT()) to return a non-child value. COLLECT allows you to filter a range within another function to return a more specific value. It might look something like:
=INDEX(COLLECT({Field to Return}, {Child Row?}, <>0, {Product Names}, [Compound/Product Name]@row), 1)
Where {Field to Return} would be the column value you'd want to return. {Child Row?} would be the helper formula I mentioned. Then {Product Names} would be the name you'd want to filter against your [Compound/Product Name]@row.
Jason Tarpinian - Sevan Technology
Smartsheet Aligned Partner
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 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!