Lookup a value and then find the largest in a range

[fazthfc]

Hi,

I have a set of data that looks like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]12[/TD]
[TD]15[/TD]
[TD]16[/TD]
[TD]10[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]6[/TD]
[TD]4[/TD]
[TD]15[/TD]
[TD]1[/TD]
[TD]19[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]22[/TD]
[TD]14[/TD]
[TD]13[/TD]
[TD]17[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]6[/TD]
[TD]8[/TD]
[TD]7[/TD]
[TD]10[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]15[/TD]
[TD]26[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD]10[/TD]
[TD]14[/TD]
[TD]19[/TD]
[TD]2[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]G[/TD]
[TD]15[/TD]
[TD]16[/TD]
[TD]20[/TD]
[TD]25[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]H[/TD]
[TD]16[/TD]
[TD]20[/TD]
[TD]22[/TD]
[TD]10[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]I[/TD]
[TD]14[/TD]
[TD]16[/TD]
[TD]19[/TD]
[TD]17[/TD]
[TD]6[/TD]
[/TR]
</tbody>[/TABLE]

In another sheet I have the list of names but not in the same order (there is a reason for that). I want a formula that will look for the Name in column A and then return the largest number for that name in columns B:F.

For example:

Forumula looks at the name sees E and returns 26.

I've tried looking at HLOOKUP, MATCH and INDEX but can't find a way of looking at the range based on a specific criteria.

Any help greatly appreciated.

 

[Kamolga]

Hi,

I use the max formula on a line and set up the number of line with a match in the column. Indirect put them together.

=MAX(INDIRECT("Sheet1!$B$"&MATCH(Sheet2!A2;Sheet1!$A$1:$A$10)&":$F$"&MATCH(Sheet2!A2;Sheet1!$A$1:$A$10)))

I can go in more details if it does not work for you

 

[RasGhul]

Hi faz,

Maybe this;

Book1
	A	B	C	D	E	F
1	Name	1	2	3	4	5
2	A	12	15	16	10	6
3	B	6	4	15	1	19
4	C	22	14	13	17	10
5	D	6	8	7	10	6
6	E	8	9	15	26	4
7	F	10	14	19	2	10
8	G	15	16	20	25	15
9	H	16	20	22	10	8
10	I	14	16	19	17	6
Sheet1

Book1
	A	B
1	Name	Max
2	A	16
3	H	22
4	C	22
5	G	25
6	E	26
7	F	19
8	D	10
9	B	19
10	I	19
Sheet2

Cell Formulas
Range	Formula
B2	{=MAX(IF(Sheet1!$A$2:$A$10=A2,Sheet1!$B$2:$F$10))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[James006]

Hello,

With your Table in Sheet1 ... and your formula in Sheet2 ...

Cell A2 : B

Cell B2 :

=MAX(OFFSET(Sheet1!$A$1,MATCH(A2,Sheet1!$A$2:$A$10,0),1):OFFSET(Sheet1!$A$1,MATCH(A2,Sheet1!$A$2:$A$10,0),5))

Hope this will help

 

[Fluff]

Another option

Book1
	A	B	C	D	E	F
1	Name	1	2	3	4	5
2	A	12	15	16	10	6
3	B	6	4	15	1	19
4	C	22	14	13	17	10
5	D	6	8	7	10	6
6	E	8	9	15	26	4
7	F	10	14	19	2	10
8	G	15	16	20	25	15
9	H	16	20	22	10	8
10	I	14	16	19	17	6
Sheet1

Book1
	A	B
2	A	16
3	C	22
4	F	19
5	G	25
Sheet3

Cell Formulas
Range	Formula
B2	=MAX(INDEX(Sheet1!$B$2:$F$10,MATCH(A2,Sheet1!$A$2:$A$10,0),0))