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      
Harry Boughen replied to narendra patel on 01-Apr-13 08:46 PM
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
Harry Boughen replied to narendra patel on 01-Apr-13 09:05 PM
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
narendra patel replied to Harry Boughen on 06-Apr-13 02:08 AM
Thanks Herry

its realy hits