Index/Match with two criteria: MATCH must use Search Type 1

SJ Sellers

Like the title says, I need to match on TWO criteria in an INDEX/MATCH function.

The two lookup criteria are:

1) Phase (this one is straightforward)

2) largest value less than or equal to a given percent

If I only needed to match on the second criteria, I could use INDEX/MATCH and sort the Source in Ascending Order by %.

I have seen multiple threads suggesting an INDEX/COLLECT approach as a solution for multiple Index/Match criteria

=INDEX(COLLECT({IntermediateMilestone}, {Milestone%}, Percent@row, {Phase}, Phase@row), 1)

The problem here is that only exact matches for Percent@row are returned.

Next, I tried to use a combination of MATCH and COLLECT.

Step 1: Collect all the Milestone%s that match the Phase Criteria

=JOIN(COLLECT({Milestone%}, {Phase}, Phase@row), ",") <= maybe problem here is delimiter?

Step 2: Use MATCH to find the Percent@row among the array / list of values returned by the above function

=MATCH(Percent@row, FunctionAbove) expanded this would be:

=MATCH(Percent@row, JOIN(COLLECT({Milestone%}, {Phase}, Phase@row), ","))

#NO MATCH is returned using this approach.

Screenshots for reference:

Lookup values:

Target sheet where Values need to be returned:

Because of complexities too detailed to describe here, we REALLY want to avoid creating multiple helper columns or dividing the lookup values into multiple different columns.

Thank you in advance for whatever help you can offer.

Mark Cronk

Good evening, Here's a longshot. I couldn't test it so cross your fingers. Try:

=INDEX(COLLECT( {milestone}, {Milestone%}, @cell=MAX(COLLECT({Milestone%}, {Milestone%}, @cell<=Percent@row, {Phase}, Phase@row), {Phase}, Phase@row), 1)

Get lucky?

Mark

Mark Cronk

Wow. So close. If you can't nest the 2 formulas then I suspect you're stuck with a helper column. Place it on the far right of the sheet and hide it. Pretend it's not there and declare success.

Mark

Mark Cronk

Good evening, Here's a longshot. I couldn't test it so cross your fingers. Try:

=INDEX(COLLECT( {milestone}, {Milestone%}, @cell=MAX(COLLECT({Milestone%}, {Milestone%}, @cell<=Percent@row, {Phase}, Phase@row), {Phase}, Phase@row), 1)

Get lucky?

Mark

SJ Sellers

This is SOOO close! Thank you Mark.

I am getting a #NESTED CRITERIA error. The Community explanation of this error only mentions SUMIF and COUNTIF so it wasn't particularly helpful. Formula Error Messages | Smartsheet Learning Center

However - If I separate the formula into two columns:

COLUMN 1: Look up the Milestone%

=MAX(COLLECT({Milestone%}, {Milestone%}, @cell <= Percent@row, {Phase}, Phase@row))

COLUMN 2: Look up the Milestone referencing Column1

=INDEX(COLLECT({Milestone}, {Milestone%}, [COLUMN 1]@row, {Phase}, Phase@row), 1)

It works.

I would really really like to get this into one column (the existing sheet is over 96 columns wide already) . Any other ideas? 😊

Thanks again for the suggestions so far! they are great!!

Mark Cronk

Wow. So close. If you can't nest the 2 formulas then I suspect you're stuck with a helper column. Place it on the far right of the sheet and hide it. Pretend it's not there and declare success.

Mark