Excel Help Required on Repeating Lookup Value

[umermalik]

Dear Experts

I have prepared a cash flow for my company and I would like to show the 5 top payments/outflows for each month in a separate table. I have done it using the "large function" and extracted the values for each month.
However, when i looked up the narration for each of the top 5 values from the cash flow column A, I am getting a same narration if a value is appearing twice or more in the values column.

Can you please check the formula in cells M8 and M9 and let me know what I am doing wrong. I will appreciate if the formula could be corrected or any other alternative formula is suggested by the members.

The formula in M8 cell is: INDEX($A$24:$A$66,AGGREGATE(15,6,(ROW($B$24:$J$66)-ROW($B$24)+1)/($B$24:$J$66=N8),COUNTIF($N$8:N8,N8)))) The result is correct (From cell A33)
The Formula in M9 cell is: INDEX($A$21:$A$63,AGGREGATE(15,6,(ROW($B$21:$J$63)-ROW($B$21)+1)/($B$21:$J$63=N6),COUNTIF($N$5:N6,N6)))) The result is wrong, it should be "ADC Cash Requirement" (from A34 cell)

The result in both the cells (M8 and M9) is same from the first matching record on row A33. The second record must be from row A34 to be correct.



Thanks and kind regards

 

[jasonb75]

Because you have multiple columns with the same value and haven't isolated the correct one. There are 8 records in row 33 that contain the same amount so the formula will not advance to the next row until the k value in aggregate reaches 9.

Try isolating the column with index and match as below, assuming that all dates are in compatible formats.

=INDEX($A$24:$A$66,AGGREGATE(15,6,(ROW($A$24:$A$66)-ROW($A$24)+1)/(INDEX($B$24:$J$66,0,MATCH($N$3,$B$20:$J$20,0))=N8),COUNTIF($N$8:N8,N8))))

Think I have that right, but only edited in the browser, not tested in excel.

 

[umermalik]

Thanks Jasonb75, you are right my formula didn't cover the the column part.
It worked perfectly.

Many thanks.