How can I exclude "child rows" when executing VLOOKUP?

KevC

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?

Jason Tarpinian

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.