Can I make this Multi-Criteria Index-Match give me the next result if it's already found one?

[RockandGrohl]

Using the formula here:

https://exceljet.net/formula/index-and-match-with-multiple-criteria

I have an index-match setup with about 5 or 6 different criteria. for simplicity sake, let's say there are only two criteria



[TABLE="class: grid, width: 800, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]FRUIT[/TD]
[TD]SIZE[/TD]
[TD]PRICE[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]BANANA[/TD]
[TD]10[/TD]
[TD]£6.50[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]BANANA[/TD]
[TD]10[/TD]
[TD]£4.80[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]BANANA[/TD]
[TD]8[/TD]
[TD]£3.60[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]BANANA[/TD]
[TD]8[/TD]
[TD]£3.45[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]












The issue here is where we have two matching criteria (FRUIT & SIZE) however there are two different prices.

On the next table where we want the results, it may be something like this:



[TABLE="class: grid, width: 800, align: left"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]FRUIT[/TD]
[TD]SIZE[/TD]
[TD]PRICE[/TD]
[TD]TIER[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]BANANA[/TD]
[TD]10[/TD]
[TD][/TD]
[TD]EXPENSIVE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]BANANA[/TD]
[TD]10[/TD]
[TD][/TD]
[TD]CHEAP[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]BANANA[/TD]
[TD]8[/TD]
[TD][/TD]
[TD]EXPENSIVE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]BANANA[/TD]
[TD]8[/TD]
[TD][/TD]
[TD]CHEAP[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]











So the problem is that it will happily match the first 2 criteria and always give me the top result, in the above case that's £6.50 and £3.60 respectively.

How do I make it so that if it's already found a match, it finds the next available unique match in the list?

Obviously my example of Apples and Bananas and whatever isn't applicable. My problem is that I have template names, sizes and days of the week it runs in which are a few different criteria, but there's still a couple situations where the criteria is the same but I need different results.

Hope this makes sense.
****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="class: grid, width: 800, align: left"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]FRUIT[/TD]
[TD]SIZE[/TD]
[TD]PRICE[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]BANANA[/TD]
[TD]10[/TD]
[TD]£6.50[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]BANANA[/TD]
[TD]10[/TD]
[TD]£4.80[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]BANANA[/TD]
[TD]8[/TD]
[TD]£3.60[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]BANANA[/TD]
[TD]8[/TD]
[TD]£3.45[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]








</body>

 

[Fluff]

This works on your example, but probably not on your real data.

Excel 2013/2016
	I	J	K	L	M
1	1	FRUIT	SIZE	PRICE	TIER
2	2	BANANA	10	6.5	EXPENSIVE
3	3	BANANA	10	4.8	CHEAP
4	4	BANANA	8	3.6	EXPENSIVE
5	5	BANANA	8	3.45	CHEAP
West creek

Cell Formulas
Range	Formula
L2	{=LARGE(IF(($A$2:$A$5=J2)*($B$2:$B$5=K2),($C$2:$C$5)),IF(M2="expensive",1,2))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Aladin Akyurek]

I don't believe it's that helpful discussing some site's formulas. The most appropriate way is to provide a small representative input and the output that must obtain for that input without reference to any formula.

If I understand correctly, you have the following input:

Book1
	A	B	C
1	FRUIT	SIZE	PRICE
2	BANANA	10	6.50
3	BANANA	10	4.80
4	BANANA	8	3.60
5	BANANA	8	3.45
Sheet1

and you desire to obtain the following output:

Book1
	A	B	C
1	FRUIT	SIZE	PRICE
2	BANANA	10	6.50
3	BANANA	10	4.80
4	BANANA	8	3.60
5	BANANA	8	3.45
Sheet2

In C2 of Sheet2 control+shift+enter, not just enter, and copy down:

=IFERROR(LARGE(IF(Sheet1!$A$2:$A$5=$A2,IF(Sheet1!$B$2:$B$5=$B2,Sheet1!$C$2:$C$5)),COUNTIFS($A$2:A2,A2,$B$2:B2,B2)),"")

Two remarks...

[1] How did you needed to look for 4 banana entries and 2 size enties per banana? (A formula system can be set up to obtain these pairs.)

[2] Try to test the suggested formula output by changing the input values.