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.
