How can I use INDEX and MATCH to find a cell using two inputs and mulptiple hits?

Question

I have a table with data. Using two inputs I want to find the first or second match and return the content of a corresponding column

Using INDEX and MATCH I'm able to find the first occurrence of matching both inputs (colour and size).

Table1
+--------+------+---------+
| colour | size |  price  |
|--------+------+---------+
|red     |   2  |   $3.00 |1
|red     |   3  |   $2.00 |2
|blue    |   3  |   $3.00 |3
|green   |   5  |   $4.00 |4
|blue    |   4  |   $6.00 |5
|red     |   4  |  $77.00 |6
|red     |   4  | $999.00 |7
+--------+---+--+---------+

I would like to have the row number with 999 returned (row 7).

I can obtain row 6 using

=INDEX(Table1,MATCH(1, INDEX((Table1[colour] = F1) *(Table1[size] = F2), 0, 1), 0),3)

I have tried to understand examples using AGGREGATE but don't understand them.

Answer 1

Try this one:

On column D create a data with the item and repetition. (For me it is not clear if you are looking for the second data or just the "n" data)

So on column D add the formula: =CONCATENATE(A3,B3,COUNTIFS($A$3:$A$10,A3,$B$3:$B$10,B3) (I am assuming that your first cell it is A3 until row 10, just custom it) In this column you will get the data:

red21

red31

blue31

green51

blue41

red41

red42

So when you are searching for the data, you can ask for the repetition you are interested in with MATCH + INDEX or VLOOKUP:

=VLOOKUP(F1&F2&F3,CHOOSE({2\1},$A$3:$A$10,$E$3:$E$10),2,0)

F3 cell will have the repetition number as you have the colour on F1 and the size on F2. If it is always the second repetition, instead of F3 place "2".

Hope it helps