Index Match or Index Collect with multiple criteria

2»

Answers

  • @Shelby Thank you for your response! I am getting #incorrectargument, but I'm probably doing this incorrectly...

    In Sheet A, I would like to notate the monthly rate for a truck, based on the "Fleet Type". So I would need to match the Truck Number from Sheet B, which also has the fleet type.


    This is Sheet B.

    I'm not sure if I've explained well, but would you mind directing me in how I should alter the formula to do this?

  • Shelby
    Shelby ✭✭✭✭✭✭

    @AnnieSE

    Can you copy/paste the formula that's entered under the "Monthly Rate" header in Sheet A?

  • @Shelby Yes, this is what I have at the moment:

    =JOIN(COLLECT([Monthly Rate per Type]61:[Monthly Rate per Type]66, {Test Internal Archive of Full Fleet Status Range 5}, [Fleet Type]61:[Fleet Type]66, {Test Internal Archive of Full Fleet Status Range 2}, [Primary Column]@row))

  • @Shelby Hello, I just wanted to follow up and see if you were still able to help?

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @AnnieSE

    If I understand you correctly, you want your formula to look into the other sheet (Sheet B) to find out what Fleet Type your Truck Number is under, then compare this Fleet Type to the chart in formula's sheet (Sheet A), to return the Monthly Rate based on this Type.


    If so, you'll want to have an INDEX(MATCH formula to find the Fleet Type across sheets. Then you can use what this returns as the Match Criteria in another INDEX(MATCH to look in the current sheet.


    Try this:

    =INDEX([Monthly Rate per Type]$61:[Monthly Rate per Type]$66, MATCH(INDEX({Test Internal Archive of Full Fleet Status Range 5}, MATCH([Primary Column]@row, {Test Internal Archive of Full Fleet Status Range 2}, 0)), [Fleet Type]$61:[Fleet Type]$66, 0))

  • Julie Harder
    Julie Harder ✭✭✭✭✭✭
    edited 07/02/21

    I'm trying to use index collect to pull a group name associated to the oldest date. I am getting it to work on my template sheet, but on my live sheets I am getting an #INVALID VALUE error. I cannot find any errors in the sheets and when I remove Status:Status, <>"Complete" I receive a value, but I need to exclude completed requests. Any help is appreciated!

    =INDEX(COLLECT(Group:Group, Status:Status, <>"Complete", Status:Status, <>"Not Received", [New, Renewal, Termed]:[New, Renewal, Termed], "Renewal", [Date Received]:[Date Received], MIN([Date Received]:[Date Received])), 1)

    To add, I'm using the Sheet Summary for these formulas


    UPDATE: Of course after posting this I figured it out. Instead of referencing MIN([Date Received]:[Date Received], I referenced my summary column pulling the oldest date.

    =INDEX(COLLECT(Group:Group, Status:Status, <>"Complete", Status:Status, <>"Not Received", [New, Renewal, Termed]:[New, Renewal, Termed], "Renewal", [Date Received]:[Date Received], [Oldest Renewal Date]#), 1)

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Julie Harder Glad you were able to get it figured out.

    thinkspi.com

  • I'm doing the same type of formula but with more than 2 criteria and I keep getting errors. Can someone help me with what I am doing incorrectly


    =INDEX(COLLECT({QA Analyst}, {Agent Oracle ID}, CONTAINS([Oracle ID]@row, @cell + ""), {Week}, CONTAINS([email protected], @cell + ""), {Evaluation #}, CONTAINS([Evaluation #]@row, @cell + ""), {Verizon QA Evaluations Range 8}, >=DATE(2022, 7, 1)), 1)

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Hollie Green The syntax look correct. What error are you getting?

    thinkspi.com

  • I'm not sure why the error occurred but it fixed itself after a few hours without me changing anything

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!