Frustrated with index

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.

A1Loan #It will return this
A2123456789I want it to return this.

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

Any help is greatly appreciated.
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
I don't follow your formula. The highlighted red "1" below (2 places) will return the first row in column E or A in the referenced workbook/sheet. I suspect you do not want that. Could you explain what you are trying to achieve? It appears that you might want to return the entry in any row just above an entry that begins with the letter G.
=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))
 
Upvote 0
I hope I'm not bringing more confusion.
But making some avaliation to your formula I only see it working as an array formula - "{".
Focusing only on the main formula : =INDEX($E:$E,1,SMALL(IF(LEFT($E:$E,1)="G",ROW($A:$A)),ROW(1:1))-1)
The index argument that you are working is the column, but your matrix only has one column "E:E".
So, can you show the sheet? It might help...
 
Upvote 0
While i would love to help with the XL2BB.... I don't think it would be wise as I work for a bank and shouldn't randomly install something without consent. But I can probably do screen shots and just change the critical info.

So what I am trying to do in another sheet is this with the formula.

If column E starts with G bring in that loan number. If not move on to the next row until it finds another G and not return any blanks.

Does this help at all?
2020-07-30_9-56-53.jpg
 
Upvote 0
Also to be completely honest with you all I don't understand my formula because I really don't understand how index works.

I just trudge through the mud till I get it to work usually.

It could be a total piece of garbage and I wouldn't know it.

#indexn00b
 
Upvote 0
I'll offer a suggestion. Here is a nonsense table to stand in for Sheet 1 data.
Book2
ABCDE
1
2
3theberry
4greatgolf
5wallhotel
6ofgarry
7chinaindia
8daisyginger
9
Sheet1

We'll examine column E in Sheet1 and find any entries that begin with the letter G and then return the contents of columns A and E in that same row.

I would recommend not operating on the entire column, and instead use a more realistic range--here I've assumed the data are found in rows 3 to 100. Rather than using IF and SMALL functions, I've opted for the AGGREGATE function, using the 1st argument of 15 to return the smallest nth value in the array. The "array" in question is formed by dividing the row index position of the data table by a logical comparison of whether the cell content in column E begins with the letter G. If so, the row index position will be an element in the array, and if not, then a division by 0 error will be entered as the array element. The 6 as the 2nd argument in the AGGREGATE function will ignore these errors, and the ROW(1:1) part of the formula will return the 1st, 2nd, 3rd, etc. smallest row indexes as the formula is copied down. The result of the AGGREGATE function will then be the row index in the Sheet1 data table where the entry in column E begins with "G".
Book2
AB
1Sheet 1 Col ASheet 1 Col E
2greatgolf
3ofgarry
4daisyginger
5
ac66bronco
Cell Formulas
RangeFormula
A2:A4A2=IFERROR(INDEX(Sheet1!$A$3:$A$100,AGGREGATE(15,6,(ROW(Sheet1!$A$3:$A$100)-ROW(Sheet1!$A$2))/(LEFT(Sheet1!$E$3:$E$100,1)="G"),ROW(1:1))),"")
B2:B4B2=IFERROR(INDEX(Sheet1!$E$3:$E$100,AGGREGATE(15,6,(ROW(Sheet1!$A$3:$A$100)-ROW(Sheet1!$A$2))/(LEFT(Sheet1!$E$3:$E$100,1)="G"),ROW(1:1))),"")
 
Upvote 0
Kirk

=IFERROR(INDEX(Sheet1!$A$3:$A$100,AGGREGATE(15,6,(ROW(Sheet1!$A$3:$A$100)-ROW(Sheet1!$A$2))/(LEFT(Sheet1!$E$3:$E$100,1)="G"),ROW(1:1))),"")

Worked beautifully!

Thank you so much!
 
Upvote 0
That's good to hear...I'm happy to help.
 
Upvote 0
I'm glad that it's solved.
But never used the AGGREGATE function. @KRice I export the functions to a sheet and I tried to understand the formula. Can I ask two questions? If so..
1. When you use AGGREGATE?
2. What's the difference between the SMALL? Because, correct if I'm wrong, the SMALL also, automatically, ignore errors arguments on your array, right? What's the advantage then?
Thank you in advance and thank you for knowing a new function.
 
Upvote 0
About those questions...sure...
I tend to use AGGREGATE when it makes sense to construct an array to find which elements in a row or column are of interest. Often this means that multiple values in a table are to be found and eventually returned to some other location, and to facilitate this process, we would like to construct an array that identifies the relevant information. Typically, this involves combining the row/column numbers or row/column index numbers in a table with one or more logical comparisons. An effective way to do this, in this particular case, is to first construct an array of row index values...within the AGGREGATE function I posted, the numerator, (ROW(Sheet1!$A$3:$A$100)-ROW(Sheet1!$A$2)), generates the array {1;2;3;...;98}. This is accomplished by referencing a range related to the rows of the data table (which would give {3;4;5;...;100}, and then subtracting the row number just prior to where the data begins (which is why I put the -ROW(Sheet1!$A$2) in the formula). This provides a reference frame, but we need to determine which of these row indexes to choose.

The numerator, (LEFT(Sheet1!$E$3:$E$100,1)="G"), does this. Since we want to identify which cells in column E of the data table begin with the letter "G", this logical test generates an array of TRUE's and FALSE's, depending on the result of the logical check. Since the row index array is divided by the logical array, the division operator coerces Excel to treat TRUE's as 1's and FALSE's as 0's. So in my short working example where only the first 6 rows of the data table hold any data, the first six elements of the formed array are, {#DIV/0!; 2; #DIV/0!; 4; #DIV/0!; 6}, and all other array elements after the 6th are #DIV/0!. This means that table row indices 2, 4, and 6 have data in column E that begins with the letter G, and all other rows are to be ignored.

AGGREGATE offers a lot of functionality--dictated by its 1st argument--but I rarely ever use it for anything other than finding the smallest (option 15) or largest (option 14) elements in an array. Frankly, I haven't had much luck using the other options and have found other Excel functions well suited in those cases. But in this case, the SMALL function doesn't play well with arrays constructed like this, and the presence of #DIV/0! errors in the array will cause SMALL to return a #NUM! error. The 2nd argument in AGGREGATE (the "6"), tells AGGREGATE to ignore these errors. So this method intentionally introduces errors for the row indices that are not relevant, and then conveniently ignores all of them by using the 2nd argument=6.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top