Frustrated with index

[ac66bronco]

I am trying to use index and if and left and linking 2 sheets together to no avail

Here is my formula.

=IF(ISERROR(INDEX('[Loan Mod Master List 07-20.xlsb]Sheet1'!$E:$E,1,SMALL(IF(LEFT('[Loan Mod Master List 07-20.xlsb]Sheet1'!$E:$E,1)="G",ROW('[Loan Mod Master List 07-20.xlsb]Sheet1'!$A:$A)),ROW(1:1))-1,1)),"",INDEX('[Loan Mod Master List 07-20.xlsb]Sheet1'!$A:$A,1,SMALL(IF(LEFT('[Loan Mod Master List 07-20.xlsb]Sheet1'!$E:$E,1)="G",ROW('[Loan Mod Master List 07-20.xlsb]Sheet1'!$A:$A)),ROW(1:1))-1,1))

I know it is partially working because it is returning a value from the other sheet. Just not the right one. I want it to bring in the next one and each one subsequently below it.

A1	Loan #	It will return this
A2	123456789	I want it to return this.

I have been working at it for a few hours to no avail.

Any help is greatly appreciated.

 

[tico_ocit]

Thank you so much for you explanation.
I mostly use SMALL to get the lowest number on the array that I build, as more or less this example we are talking about. I use a lot as array formula too.
But I agree that the AGGREGATE formula is much more cleaner, and at the same time work around the error arguments, which sometimes can give you a headache.