Cross-Sheet Formula Help

Alex Knisely

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

Paul Newcome

Are you able to provide some screenshots for reference?

Alex Knisely

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.

Current Enrollment Sheet (Intake Form).png

Minimum Enrollment Base Sheet.png

Paul Newcome

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.

Alex Knisely

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

Paul Newcome

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