ac66bronco
New Member
- Joined
- Dec 10, 2014
- Messages
- 29
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.
I have been working at it for a few hours to no avail.
Any help is greatly appreciated.
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.