IF/INDEX/COLLECT
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
-
@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)
-
@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
Answers
-
=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.
-
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
-
@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.
-
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.
-
@L_123 I think if the ranges don't match you would end up with #INCORRECT ARGUMENT SET.
-
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 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)
-
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.
-
Once I removed the last criterion ({Instructor Currency_Instructor Fee},ย Country@row) the formula worked for me.
Thank you!
-
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
-
@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
-
Help Article Resources
Categories
Check out the Formula Handbook template!
