# Cross-Sheet Formula Help

✭✭✭

Hello,

I'm trying to create a cross sheet formula that looks at the content of multiple columns in a row on one sheet and looks to another sheet to confirm those values are present, and then returns a value in that sheet.

I've tried VLOOKUP and an IF statement, but I'm not able to get it to work properly. FYI - within each column there may be duplicate values, however, uniqueness can be achieved by combining the information from multiple columns.

Thanks,

Alex

Tags:

• ✭✭✭✭✭✭

Are you able to provide some screenshots for reference?

• ✭✭✭

Hi Paul - that would be a great idea.

I'd like to use the two columns highlighted in yellow in the Current Enrollment Sheet (Intake Form) and compare where they match the two highlighted in yellow columns in the Minimum Enrollment Base, provide the Min.Enrollment value, in the Min. Enrollment value in the Current Enrollment Sheet.

• ✭✭✭✭✭✭

You are going to want to try an INDEX/COLLECT.

=INDEX(COLLECT({Source Sheet Min Value Column}, {Source Sheet Prod Sub Group Column}, @cell = [Prod Sub Group]@row, {Source Sheet Dropdown Column}, @cell = [Prod Type]@row), 1)

Of course you are going to want to change column names so they match what is in your sheet. I just typed in something quick to give you an idea of what goes where.

• ✭✭✭

Hi Paul - thanks so much for offering help. The INDEX(COLLECT formula seems to be a great idea. I've tried following the logic in your formula but am still arriving at #unparsable.

Any suggestions for the below:

=INDEX(COLLECT({PMCF Plans & Commitments Range 5}, {PMCF Plans & Commitments Range 6}, @cell = [Productsub-group]@row5, {PMCF Plans & Commitments Range 7}, @cell = [ProductType]@row5, 1)

Many thanks!

Alex

• ✭✭✭✭✭✭

@Alex Knisely You need to use either "@row" OR "5". Not both.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!