same formula two tabs different result?

[kindred21101982]

Hello all

I'm a basic MS Excel user.

I'm having some issues with the below array formula. I have a sheet called 4a where the formula is located. The formula works well in sheet 4a.

=IFERROR(INDEX($'3'.$I$2:$I$9999,SMALL(IF($'3'.$B$2:$B$9999=$'4'.$B$9,ROW($'3'.$B$2:$B$9999)-ROW($'3'.$B$2)+1),ROWS($'3'.$B$2:$B3))),"")

However! when I change the formula to the below it doesn't work. I'm not sure why.

=IFERROR(INDEX($'3'.$I$2:$I$9999,SMALL(IF($'3'.$B$2:$B$9999=$'5'.$B$9,ROW($'3'.$B$2:$B$9999)-ROW($'3'.$B$2)+1),ROWS($'3'.$B$2:$B3))),"")

The only change is the formula is used in sheet 5a and $'4'.$B$9 becomes $'5'.$B$9. The sheets are identical. The data is identical. I'm not sure why it isn't working.

$'4'.$B$9 points to a drop down box (list). The data in the list is identical to sheet 4. Not sure what's happening.


Any thoughts?

Regards
kinred21101982

 

[kindred21101982]

I don't have excel at home the formula has been alter slightly to work on Open Office same formula more or less.

 

[kindred21101982]

Below is what the formula looks like in MS Excel. As previously stated, it works on one sheet. On another sheet with the search field changed it doesn't work.

=IFERROR(INDEX('3'!$I$2:$I$9999,SMALL(IF('3'!$B$2:$B$9999='4'!$B$9,ROW('3'!$B$2:$B$9999)-ROW('3'!$B$2)+1),ROWS('3'!$B$2:$B3))),"")