Index match formula for maximum and first values

[Tom N]

Hello
I am using index/match and related formulas to find the largest, first, and last values among the groups of values.
Column B gives an ID for the rows grouped together for the comparisons.
The columns C, D, and E list the values to be compared. My formula using offset/index/match works for finding the last value in the group but I haven’t been able how to capture the first values with the parameters of index/match and the highest values for columns F and G with the Max function.. I have highlighted the appropriate values in C, D, and E that the formulas should return.The values for column C go to column F, D to G, and E to H.

ColumnF =OFFSET(INDEX($B2:$B23,MATCH($B2,$B2:$B$23,1),0),0,1)
ColumnG = =MAX(OFFSET(INDEX($B2:$B23,MATCH($B2,$B2:$B$23,1),0),0,2))
ColumnH= =OFFSET(INDEX($B2:$B23,MATCH($B2,$B2:$B$23,1),0),0,3)

A	B	C	D	E	F	G	H
1	2012.52	1.0332	1.0344	1.0282	1.0311	1.0340	1.0337
2	2012.52	1.0286	1.0314	1.0303	1.0311	1.0340	1.0337
3	2012.52	1.0303	1.0329	1.0311	1.0311	1.0340	1.0337
4	2012.52	1.0311	1.034	1.0337	1.0311	1.0340	1.0337
5	2013.01	1.0326	1.0356	1.0315	1.0337	1.0351	1.0343
6	2013.01	1.0332	1.0355	1.0348	1.0337	1.0351	1.0343
7	2013.01	1.0346	1.0369	1.0336	1.0337	1.0351	1.0343
8	2013.01	1.0337	1.0351	1.0343	1.0337	1.0351	1.0343
9	2013.02	1.0362	1.0365	1.0355	1.0412	1.0414	1.0376
10	2013.02	1.0354	1.0368	1.036	1.0412	1.0414	1.0376
11	2013.02	1.036	1.0386	1.0376	1.0412	1.0414	1.0376
12	2013.02	1.0374	1.0437	1.0413	1.0412	1.0414	1.0376
13	2013.02	1.0412	1.0414	1.0376	1.0412	1.0414	1.0376
14	2013.03	1.0365	1.0416	1.0387	1.0388	1.0431	1.0419
15	2013.03	1.0387	1.0399	1.0391	1.0388	1.0431	1.0419
16	2013.03	1.0393	1.0424	1.0415	1.0388	1.0431	1.0419
17	2013.03	1.0415	1.0417	1.0388	1.0388	1.0431	1.0419
18	2013.03	1.0388	1.0431	1.0419	1.0388	1.0431	1.0419
19	2013.04	1.0416	1.0451	1.0433	1.0528	1.0538	1.0474
20	2013.04	1.0433	1.05	1.0473	1.0528	1.0538	1.0474
21	2013.04	1.0473	1.0543	1.0528	1.0528	1.0538	1.0474
22	2013.04	1.0528	1.0538	1.0474	1.0528	1.0538	1.0474

<tbody>
</tbody>

Thank you in advance.

Tom

 

[Peter_SSs]

OFFSET is a volatile function so is best avoided where possible.

It is good to show what you have tried but also good to show the expected results, rather than the wrong results which I think is what you have shown for the last two columns.

Do these formulas, copied down do what you want?
If not explain which ones are wrong, what the correct values should be and how you (manually) get those values.

H2 is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down.

Excel Workbook
	A	B	C	D	E	F	G	H
1	#	ID	Val 1	Val 2	Val 3	First	Last	Largest
2	1	2012.52	1.0332	1.0344	1.0282	1.0332	1.0337	1.0344
3	2	2012.52	1.0286	1.0314	1.0303	1.0332	1.0337	1.0344
4	3	2012.52	1.0303	1.0329	1.0311	1.0332	1.0337	1.0344
5	4	2012.52	1.0311	1.034	1.0337	1.0332	1.0337	1.0344
6	5	2013.01	1.0326	1.0356	1.0315	1.0326	1.0343	1.0369
7	6	2013.01	1.0332	1.0355	1.0348	1.0326	1.0343	1.0369
8	7	2013.01	1.0346	1.0369	1.0336	1.0326	1.0343	1.0369
9	8	2013.01	1.0337	1.0351	1.0343	1.0326	1.0343	1.0369
10	9	2013.02	1.0362	1.0365	1.0355	1.0362	1.0376	1.0437
11	10	2013.02	1.0354	1.0368	1.036	1.0362	1.0376	1.0437
12	11	2013.02	1.036	1.0386	1.0376	1.0362	1.0376	1.0437
13	12	2013.02	1.0374	1.0437	1.0413	1.0362	1.0376	1.0437
14	13	2013.02	1.0412	1.0414	1.0376	1.0362	1.0376	1.0437
15	14	2013.03	1.0365	1.0416	1.0387	1.0365	1.0419	1.0431
16	15	2013.03	1.0387	1.0399	1.0391	1.0365	1.0419	1.0431
17	16	2013.03	1.0393	1.0424	1.0415	1.0365	1.0419	1.0431
18	17	2013.03	1.0415	1.0417	1.0388	1.0365	1.0419	1.0431
19	18	2013.03	1.0388	1.0431	1.0419	1.0365	1.0419	1.0431
20	19	2013.04	1.0416	1.0451	1.0433	1.0416	1.0474	1.0543
21	20	2013.04	1.0433	1.05	1.0473	1.0416	1.0474	1.0543
22	21	2013.04	1.0473	1.0543	1.0528	1.0416	1.0474	1.0543
23	22	2013.04	1.0528	1.0538	1.0474	1.0416	1.0474	1.0543
First Last Largest

 

[Tom N]

That's exactly what I needed the formula to do. It is amazing how simple your formula is!
PS I will keep working with the posting tools provided, but I have been having trouble with them.
Thank you for your help.

 

[Peter_SSs]

That's exactly what I needed the formula to do. It is amazing how simple your formula is!

Thank you for your help.

You are very welcome. Glad it helped.

PS I will keep working with the posting tools provided, but I have been having trouble with them.

Have a look in the About This Board forum to see if you can find any other threads that deal with the particular problem(s) you are having. If you cannot find anything, then I'd suggest that you post your own thread there and detail your Excel version, operating system, which of the tools you are trying and exactly what problem(s) you are running into. There are very few people who cannot eventually get at least one of the tools up and running. Good luck!