Microsoft Excel - index match to find max no. - Asked By narendra patel on 01-Apr-13 06:16 AM

I WANT TO INDEX MACH IN MAXIMUM NUMBER FIND. ABOVE EXAMPAL I ENTER VALUE (13) IN CELL F2 SO I WANT TO RESULT MAXIMUM NUBER OF COLUNUM BILLNO =6. SECONDLY I ENTER VALUE 14 SO RESULT MAXI. 8

	A	B	C	D	E	F	G	H
1	BILLNO	NM	NN	MNM
2	3	ASS	FGD	RTR	13	13
3	4	SDS	DFD	TTN	13
4	5	DSS	HJ	YYR	13	If I enter in cell f3 = 13 so I want to result maximum bill no. = 6 , and 14 enter to  result = 8, and 15 to 7 result
5	6	DDF	SD	TYU	13
6	4	CDS	ASD	TTY	14
7	5	SFF	SF	YFF	14
8	8	FSA	HF	SDF	14
9	3	DFF	SA	YT	15
10	4	DAA	YR	WE	15
11	5	GDD	ASK	DRW	15
12	7	HFF	IY	SD	15

Hello Narendra,
Try
=OFFSET($A$1,MATCH($F$2,E:E)-1,0)
This will only work if the numbers in ColumnE (and Column A) are in strict sequence as shown in your example.
Regards
Harry

Hello Narendra,
This formula will work if the numbers are not in strict order as well

{=MAX(OFFSET($A$1,1,0,COUNT(A:A),1)*(OFFSET($E$1,1,0,COUNT(A:A),1)=$F$2))}

This has to be entered as an array formula using CTRL/SHIFT/ENTER

Regards
Harry

Thanks Herry

its realy hits