Please let me understand the formula given below

[Gaurav Tripathi]

Dear All,

I am not able to understand the given formula completely:

=IFERROR(INDEX(Entry!$J$6:$J$1234,SMALL(IF(Entry!$BA$6:$BA$1234='Daily Report'!$K$2,ROW(Entry!$J$6:$J$1234)),ROWS('Daily Report'!$F$33:F33))-ROW(Entry!$J$6)+1),"")

I can't understand underlined part. It refers to cell number B33 of sheet "Daily Report".
What is +1 at end?

 

[Mike H..]

Dear All,

I am not able to understand the given formula completely:

=IFERROR(INDEX(Entry!$J$6:$J$1234,SMALL(IF(Entry!$BA$6:$BA$1234='Daily Report'!$K$2,ROW(Entry!$J$6:$J$1234)),ROWS('Daily Report'!$F$33:F33))-ROW(Entry!$J$6)+1),"")

I can't understand underlined part. It refers to cell number B33 of sheet "Daily Report".
What is +1 at end?

Hi,

I would write this formula slightly differently like this:-

=IFERROR(INDEX(Entry!$J$6:$J$1234,SMALL(IF(Entry!$BA$6:$BA$1234='Daily report'!$K$2,ROW($J$6:$J$1234)),ROWS($F$1:F1))-ROW($J$6)+1),"")

Note in the bold and underlined bit I've removed all the references to a worksheet because they're not necessary. These bits of the formula are used for counting only.

Note also I changed ROWS(F33:F33) to ROW(F1:F1) because both of these evaluate as 1 and I think mine is more intuitive.


ROW($F$1:F1) evaluates as 1 and as the formula is drag down it becomes $F1:F2 and evaluates as 2 etc. ROW(J6)+1 is returning 7 and this doesn't change as the formula is dragged down.

All these numbers are doing is correcting for the data starting in row 6 and incrementing by 1 for each time the formula is dragged down so it returns the second and third match etc.

 

[Gaurav Tripathi]

Dear Mike,

Thanks for the explanation. Now I can manipulate it as per my requirement.

Regards,

Gaurav