Convert VLOOKUP to INDEX/COLLECT?

rebzleb

Hi, I am trying to calculate the fees for a project based a list of fees from another sheet. My current formula works when only one product is selected but does not work for multiple products. Would converting this to INDEX/COLLECT work? And if so, how would I do it?

Current formula looks up the value in Project Type/Scope on the Project Fees sheet and brings in the value from column 3. If the number of sites is >1 it adds the value from column 4, times the number of sites minus one

(VLOOKUP([Project Type/Scope]@row, {Project Fees}, 3, false)) + (IF([Number of Sites]@row > 1, ([Number of Sites]@row - 1), 0) * VLOOKUP([Project Type/Scope]@row, {Project Fees}, 4, false))

Columns being used - formula is in estimated fees column (ignore SOW#)

Project Type/Scope is a multi select dropdown

Project Fees sheet

SSFeatures

Hi,

Yea I think you want to use COLLECT here to make this work.

For example:

=COLLECT({Fee Range}, {Lookup Range}, Contains(@cell, [Project Type/Scope]@row))

This will collect you all of the fees by comparing the selections in the multi drop down with the Lookup rows

You can SUM this to get the total fees for the first part of your equation.

=SUM(COLLECT({Fee Range}, {Lookup Range}, Contains(@cell, [Project Type/Scope]@row)))

You can follow the same pattern for the last part of your equation:

=SUM(COLLECT({Additional Fee Range}, {Lookup Range}, Contains(@cell, [Project Type/Scope]@row)))

Are the Additional Site Fees always the same?

Hope this helps!

SSFeatures

SSFeatures

Hi,

Yea I think you want to use COLLECT here to make this work.

For example:

=COLLECT({Fee Range}, {Lookup Range}, Contains(@cell, [Project Type/Scope]@row))

This will collect you all of the fees by comparing the selections in the multi drop down with the Lookup rows

You can SUM this to get the total fees for the first part of your equation.

=SUM(COLLECT({Fee Range}, {Lookup Range}, Contains(@cell, [Project Type/Scope]@row)))

You can follow the same pattern for the last part of your equation:

=SUM(COLLECT({Additional Fee Range}, {Lookup Range}, Contains(@cell, [Project Type/Scope]@row)))

Are the Additional Site Fees always the same?

Hope this helps!

SSFeatures

rebzleb

thank you for this example! I created this formula but it is calculating zero for all rows

=SUM(COLLECT({Project Fees Range 2}, {Project Fees Range 3}, CONTAINS(@cell, [Project Type/Scope]@row)))

The Additional site fees differ depending on the product in the Project Type/Scope field.

rebzleb

Never mind! I figured out my error. Thank you!

SSFeatures

You're welcome! Glad you got it working.