My 'Log' worksheet references my 'funding string' worksheet. The 'funding string' worksheet contains up to 9 of the same values that will match on "N?" in my Log file. I need it to return the final value in the row from the "funding string" worksheet until no more values are found.
Am using the following formula (as an array):
=INDEX('FUNDING STRING LIST'!$M$3:$M$1520,SMALL(IF('FUNDING STRING LIST'!$C$3:$C$1520=$N6,ROW('FUNDING STRING LIST'!$C$3:$C$1520)-MIN(ROW('FUNDING STRING LIST'!$C$3:$C$1520))+1,""),ROWS('FUNDING STRING LIST'!M$3:M3)))
When the value in N changes in the current worksheet, the next highest value from the "funding string" worksheet populates as I want. The formula changes based on the row it is in. For example, the next row's formula is:
=INDEX('FUNDING STRING LIST'!$M$3:$M$1520,SMALL(IF('FUNDING STRING LIST'!$C$3:$C$1520=$N7,ROW('FUNDING STRING LIST'!$C$3:$C$1520)-MIN(ROW('FUNDING STRING LIST'!$C$3:$C$1520))+1,""),ROWS('FUNDING STRING LIST'!M$3:M4)))
Once the value in 'N' changes to another value, #NUMs are returned. The only way to get the formula to return the correct value is to "reset" the formula so that the last "rows" function is as follows:
=INDEX('FUNDING STRING LIST'!$M$3:$M$1520,SMALL(IF('FUNDING STRING LIST'!$C$3:$C$1520=$N11,ROW('FUNDING STRING LIST'!$C$3:$C$1520)-MIN(ROW('FUNDING STRING LIST'!$C$3:$C$1520))+1,""),ROWS('FUNDING STRING LIST'!M$3:M3)))
I have attached my file and highlighted the cells with the formula in question. Any guidance will be much appreciated!
Thanks in advance,
Amy
Bookmarks