Index Match or Index Collect with multiple criteria

AnnieSE · 04/21/21

@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 · 04/21/21

@AnnieSE

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

AnnieSE · 04/22/21

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

AnnieSE · 04/27/21

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

Genevieve P. · 04/28/21

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 · 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 · 07/06/21

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

Hollie Green · 07/20/22

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(Week@row, @cell + ""), {Evaluation #}, CONTAINS([Evaluation #]@row, @cell + ""), {Verizon QA Evaluations Range 8}, >=DATE(2022, 7, 1)), 1)

Paul Newcome · 07/21/22

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

Hollie Green · 07/22/22

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

gwenjo · 05/31/23

In case this helps anyone, I spent about half an hour troubleshooting a similar issue today where I kept receiving #INCORRECT ARGUMENT SET when trying the INDEX(COLLECT()) formula. The issue was not in my formula syntax, but rather that the ranges I was referencing in a separate sheet included some empty rows.

Index Match or Index Collect with multiple criteria

Answers

Help Article Resources

Categories