Index with Multiple Critera

Andrea Westrich

Hello,

I'm trying to Index information from another sheet that meets multiple criteria. All columns in the other sheet match the one below.

In the Agent cell, I want to index from the other sheet (Master Sales) the Agent name If Status is either Closed or Pending and If Weeknumber = Weeknumber @row.

As you see, I'm getting incorrect Argument, so I'm sure my choice of functions is not correct.

Is this possible?

Meagan Harte

I had this same issue all week and just figured it out - I was trying to use VLOOKUPS, MATCH, IFERROR... and it was exhausting! I hope this helps.

You'll want to use both INDEX & COLLECT. This is how I did mine:

=INDEX(COLLECT({Project Training Request Form - Impacts}, {Project Training Request Form - Business Area}, [Impacted Areas]@row, {Project Training Request Form - Project Name}, [Project Name]@row), 1)

I wanted the sheet to push out if the we had received a "yes" or a "no" on training impacts.

So you will use =INDEX(COLLECT({COLUMN YOU ARE LOOKING TO COLLECT FROM}, {CRITERIA RANGE 1}, "CRITERIA 1", {CRITERIA RANGE 2}, "CRITERIA 2", ...ETC), 1)

So for you, this is what I think might work?

=INDEX(COLLECT({AGENT NAME COLUMN}, {STATUS}, "CLOSED", {STATUS}, "PENDING", {WEEKNUMBER}, =WEEKNUMBER@ROW), 1)

I'm guessing here with what you've laid out, but I THINK this might work! Good luck!!!

(PS - here is a link to a forum that I found helpful! https://community.smartsheet.com/discussion/70359/multiple-criteria-for-index-match-or-vlookup)

Andrea Westrich

@Meagan Harte Thank you!

Two issues: When I use the Index/Collect with one criteria for Status and the weeknumber, it returns one name. When I add in "Pending" I get Invalid Value.

Do I need to do something else for the "Or" condition or "Is one of"?

The second issue, I realize this formula will and should return more than one value at times. Is it possible to do that and put them in separate rows?

Thanks so much for your help!

Meagan Harte

@Andrea Westrich - You are welcome!

For your first question, I found out from my question that @Paul Newcome kindly answered - you'll need to put an IFERROR function with that formula to avoid the "INVALID VALUE" response you're getting.

=IFERROR(INDEX(COLLECT({AGENT NAME COLUMN}, {STATUS}, "CLOSED", {STATUS}, "PENDING", {WEEKNUMBER}, =WEEKNUMBER@ROW), 1), "")

For your second, I'm not sure - and that's a great question. I'm wondering if @Paul Newcome can help?

Paul Newcome

The issue with this formula:

=IFERROR(INDEX(COLLECT({AGENT NAME COLUMN}, {STATUS}, "CLOSED", {STATUS}, "PENDING", {WEEKNUMBER}, =WEEKNUMBER@ROW), 1), "")

is that you are searing for a status that is both closed AND pending at the same exact time which is not possible. To have it search for one or the other, you are going to want to incorporate an OR function like so... Also note that "@row" is case sensitive. "@ROW" with throw the unparseable error.

=IFERROR(INDEX(COLLECT({AGENT NAME COLUMN}, {STATUS}, OR(@cell = "CLOSED", @cell = "PENDING"), {WEEKNUMBER}, =WEEKNUMBER@row), 1), "")

For part two... You would need to use a parsing solution instead. I believe @L@123 has an INDEX based formula that doesn't require the helper columns that my parsing solution does.