Lookup a value and then find the largest in a range

[fazthfc]

Hi,

I have a set of data that looks like this:

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

<tbody>
</tbody>

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))