IF/INDEX/COLLECT

HHansford

Hi,

I need some kind of IF/INDEX/COLLECT formula but the level of complexity have left me lost.

What I'm trying to do is return a fee based on the country/currency matching, as well as name, course and format.

I can happily create an INDEX/COLLECT to match the Instructor Name, Course, Format and Country/Currency to return the Fee. However, if the Country/Currency don't match, I want to return the data in a different column (Converted Fee).

Below is my starting point.

=INDEX(COLLECT({Fee_Instructor Fee}, {Instructor Name_Instructor Fees}, Instructor@row, {Course_Instructor Fees}, Course@row, {Format_Instructor Fees}, Format@row, {Instructor Currency_Instructor Fee}, Country@row), 1)

How do I build in returning the Converted Fee column if the Country/Currency don't match?

Screenshots attached.

Thanks,

Hannah

Paul Newcome

@HHansford Ah. Ok. So if the currency and country match then pull fee. If they do not match then pull converted. Give this a try...

=INDEX(COLLECT(IF([Instructor Currency]@row = Country@row, {Fee_Instructor Fee}, {Converted Fee_Instructor Fees}), {Instructor Name_Instructor Fees}, Instructor@row, {Course_Instructor Fees}, Course@row, {Format_Instructor Fees}, Format@row, {Instructor Currency_Instructor Fee}, Country@row), 1)

Paul Newcome

@HHansford You would use the same formula to pull in the amount and then multiply it by the value in [Total Students]@row like so:

=original_formual * [Total Students]@row

L_123

=INDEX(COLLECT(if([instructor currency]@row = "USD",{Fee_Instructor Fee},{converted fee}), {Instructor Name_Instructor Fees}, Instructor@row, {Course_Instructor Fees}, Course@row, {Format_Instructor Fees}, Format@row, {Instructor Currency_Instructor Fee}, Country@row), 1)

You need a new column that determines the reference (in my formula example i added the column "instructor currency" to the result sheet), then you can add an if to the return statement of your collect formula.

@Paul Newcome Thought you might like this one. Not often I find an application for a conditional range on a collect formula.

HHansford

@L_123

Thank you, I see what you did there. It is returning an #INVALID VALUE error. IS there something extra in the formula?

I made the helper column. This is the formula based on what you wrote:

=INDEX(COLLECT(IF([Instructor Currency]@row = "USD", {Fee_Instructor Fee}, {Converted Fee_Instructor Fees}), {Instructor Name_Instructor Fees}, Instructor@row, {Course_Instructor Fees}, Course@row, {Format_Instructor Fees}, Format@row, {Instructor Currency_Instructor Fee}, Country@row), 1)

Thanks,

Hannah

Paul Newcome

@L_123 I love doing that. I also use it in a lot of other functions such as COUNTIFS and SUMIFS. What's really fun is when you have a nested IF for a handful of ranges and then another nested IF for the criteria.

@HHansford That particular error coming from this formula is essentially the same as #NO MATCH. Try applying a filter to the source sheet that would show all rows that match the range/criteria sets for the data that is currently throwing the area and see how many rows are shown.

L_123

Double check your ranges and make sure they all contain the same number of references.

I second guessed myself and thought the formula wouldn't work cross-sheet, but I built a test, and my example functions as intended.

Paul Newcome

@L_123 I think if the ranges don't match you would end up with #INCORRECT ARGUMENT SET.

HHansford

smartsheet 3.PNG

I can get the formula to partially work. I think I've missed a part of the explanation of what I'm trying to do.

Using the data in the attached images, what I should see is the following instructor fees:

Row 1 $8000

Row 2 $4080

Row 3 $3225

Row 4 $2960

Row 5 $4000

Only Row 1 is correct with the current formula.

These are the different combinations I want the formula to calculate (along with matching the instructor name and format):

If Instructor Currency is USA and Country is USA then return the Fee column

If Instructor Currency is USA and Country is Canada then return the Converted Fee column

If Instructor Currency is Canada and Country is Canada then return the Fee column

If Instructor Currency is Canada and Country is Canada then return the Converted Fee column

In other words, if the Instructor Currency and Country match, return Fee, otherwise return Converted Fee.

I hope you can help me through this one!

Thanks,

Hannah

HHansford

Paul Newcome

@HHansford Ah. Ok. So if the currency and country match then pull fee. If they do not match then pull converted. Give this a try...

=INDEX(COLLECT(IF([Instructor Currency]@row = Country@row, {Fee_Instructor Fee}, {Converted Fee_Instructor Fees}), {Instructor Name_Instructor Fees}, Instructor@row, {Course_Instructor Fees}, Course@row, {Format_Instructor Fees}, Format@row, {Instructor Currency_Instructor Fee}, Country@row), 1)

L_123

I agree with @Paul Newcome, I misunderstood the original question, with the clarification it makes sense. Let us know @HHansford if this works for you or not.

HHansford

@Paul Newcome @L_123

Once I removed the last criterion ({Instructor Currency_Instructor Fee}, Country@row) the formula worked for me.

Thank you!

Paul Newcome

Happy to help. 👍️

HHansford

Hi @Paul Newcome,

I need to develop this formula further to make it work in another column. Hoping you can help me with that.

The new column is for calculating the cost of manuals needed for each class. The fee for an individual manual is listed in the source sheet (again, with a Converted Fee if the Country does not match the Instructor Currency).

For the original formula I'm able to pull in the course Fee once, because the cost if for each individual class. However for course manuals, I need the formula to calculate the total cost of manuals (one per student). Is there a way to add to the existing formula to make it multiply the Manual Fee by Total Students?

Ideal world we could do this in the existing formula but I'm open to helper columns or sheet.

Screen shots attached.

Thank you!

Hannah

Paul Newcome

@HHansford You would use the same formula to pull in the amount and then multiply it by the value in [Total Students]@row like so:

=original_formual * [Total Students]@row

HHansford

@Paul Newcome

It was that easy?! Thank you!!

Paul Newcome

Happy to help. 👍️