Referring to the second matching cell

[spark.tsang]

In the enclosed worksheet, rows 2-7 lies the data of "brown", while rows 8-22 lies the data of "yellow" and so on. On Column C, we use an INDEX MATCH formula to get the first corresponding data in each case. Similarly on column D, the last corresponding data are retrieved.

Now how we should get the second corresponding data, for instance in E8?

[AliGW]

This will pull through the first match. Drag copy it down to pull the subsequent items:

=INDEX($B$2:$B$30,MATCH("yellow",$A$2:$A$30,0)+ROWS(A$1:A1)-1)

This will specifically pull the second item:

=INDEX($B$2:$B$30,MATCH("yellow",$A$2:$A$30,0)+ROWS(A$1:A2))

[spark.tsang]

Thanks, my intention is to retrieve the first/second/last data of each "type" for further calculation using the same general formula which can be dragged down no matter which "type" area the cells are in. The desired results are such that I get the output "2" in E2:E7, the output "16.27" in E8:E22, "34" in E23:E30, using the same formula within column E, just like the way I got the first and last values on column C and D. I had no luck using the formula you provided to get the desired results. Hope a further revision will do. Thanks again.

[AliGW]

Please provide the workbook where you tried to use my formulae. There's no point in my revising the formulae - I checked them and they worked here on your file (see column F below), so I need to see what you've done wrong with them.

Excel 2016 (Windows) 32 bit

	A	B	C	D	E	F
1	Type	Data
2	brown	1	1	6
3	brown	2	1	6
4	brown	3	1	6
5	brown	4	1	6
6	brown	5	1	6
7	brown	6	1	6
8	yellow	12.24	12.24	26.54		12.2417285
9	yellow	16.27	12.24	26.54		16.268007
10	yellow	13.91	12.24	26.54		13.9073951
11	yellow	25.10	12.24	26.54		25.1023603
12	yellow	19.49	12.24	26.54		19.4923601
13	yellow	26.86	12.24	26.54		26.8604122
14	yellow	23.19	12.24	26.54		23.1905406
15	yellow	20.34	12.24	26.54		20.3356813
16	yellow	17.31	12.24	26.54		17.3119602
17	yellow	21.92	12.24	26.54		21.9208407
18	yellow	28.80	12.24	26.54		28.7960745
19	yellow	30.22	12.24	26.54		30.2187704
20	yellow	26.91	12.24	26.54
21	yellow	20.88	12.24	26.54
22	yellow	26.54	12.24	26.54
23	green	12	12	90
24	green	34	12	90
25	green	45	12	90
26	green	56	12	90
27	green	67	12	90
28	green	78	12	90
29	green	89	12	90
30	green	90	12	90

Sheet: E

[spark.tsang]

As I said, the desired results are displaying the second data all the way according to the "type". Just like in column C, the first data of every type are displayed. Now I want the second data to be displayed.

I manually entered the desired results for clarification. Please check.

[AliGW]

In E2 copied down:

=INDEX($B$2:$B$30,MATCH(A2,$A$2:$A$30,0)+1)

Third value:

=INDEX($B$2:$B$30,MATCH(A2,$A$2:$A$30,0)+2)

Fourth value:

=INDEX($B$2:$B$30,MATCH(A2,$A$2:$A$30,0)+3)

and so on.

[spark.tsang]

Perfect now, thanks a lot!

[AliGW]

Glad to have helped, but I am a bit surprised that you didn't work it out from what I gave you in post #2.

Be aware that it will only work if your data is laid out in the way it is in your sample file, with items that are yellow together, brown together and so on. If this is not the case, it will be more complex.