IF/INDEX/COLLECT

Options

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

Best Answers

«1

Answers

  • L_123
    L_123 ✭✭✭✭✭✭
    edited 10/27/22
    Options

    =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
    Options

    @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
    Paul Newcome ✭✭✭✭✭✭
    Options

    @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
    L_123 ✭✭✭✭✭✭
    Options

    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
    Paul Newcome ✭✭✭✭✭✭
    Options

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

  • HHansford
    HHansford ✭✭
    edited 10/28/22
    Options

    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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    @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
    L_123 ✭✭✭✭✭✭
    Options

    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
    Options

    @Paul Newcome @L_123


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

    Thank you!

  • HHansford
    Options

    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
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    @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
    Options

    @Paul Newcome


    It was that easy?! Thank you!!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!