Index Match or Index Collect with multiple criteria

2Next

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?

  • ShelbyShelby ✭✭✭✭✭

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

  • 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 HarderJulie 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 NewcomePaul Newcome ✭✭✭✭✭

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

    thinkspi.com

Sign In or Register to comment.