Want to find Non-Array Formula Equivalent of this....

[TheSeeker]

Hi all,

New to the forum and my reason for being here is I just created this lookup function in my excel spreadsheet. This function is placed in at least 1200 cells in at least 9 different sheets. Meaning A LOT of calculation and frankly, I don't feel like waiting 5 minutes to save the workbook (because I turned calculations to manual).

I'm trying to find an equivalent NON-ARRAY formula that can give me the same result as the array formula below. If someone could help me out, that would be amazing.

{=IFERROR(INDEX([IL.xlsx]IL!$A$1:$E$1600,SMALL(IF([IL.xlsx]IL!$C:$C=E2,ROW([IL.xlsx]IL!$A$1:$E$1600)),ROW($1:$1)),5)&" "&TEXT(INDEX([IL.xlsx]IL!$A$1:$E$1600,SMALL(IF([IL.xlsx]IL!$C:$C=E2,ROW([IL.xlsx]IL!$A$1:$E$1600)),ROW($1:$1)),2),"mm/dd/yyyy")," ")}

 

[bosco_yip]

Try this non-array formula (un-tested),

=IFERROR(INDEX([IL.xlsx]IL!$E$1:$E$1600,MATCH(E2,[IL.xlsx]IL!$C$1:$C$1600,0))&" "&TEXT(INDEX([IL.xlsx]IL!$B$1:$B$1600,MATCH(E2,[IL.xlsx]IL!$C$1:$C$1600,0)),"mm/dd/yyyy"),"")

Regards

 

[Chihiro]

Please read this.
http://chandoo.org/forum/threads/new-users-please-read.294/

• For the best/fastest results, Upload a Sample File using the "Upload a File" button at the bottom of the page.

One thing you can do to speed things up... Don't use entire column (ex. $C:$C).

Have a read of link below.
https://www.cogniview.com/articles-resources/speed-up-your-slow-spreadsheets

Alternately MS Query or PowerQuery will most often yield faster result (especially when list is not sorted on Key).

 

[TheSeeker]

Try this non-array formula (un-tested),

=IFERROR(INDEX([IL.xlsx]IL!$E$1:$E$1600,MATCH(E2,[IL.xlsx]IL!$C$1:$C$1600,0))&" "&TEXT(INDEX([IL.xlsx]IL!$B$1:$B$1600,MATCH(E2,[IL.xlsx]IL!$C$1:$C$1600,0)),"mm/dd/yyyy"),"")

Regards

So this worked, but only for the first entry. The [IL.xlsx] data it's pulling from has the same "E2" value but different E & B columns per appearance of the "E2" value. So that's why the SMALL(IF( function was put in there

Working on making a mock up of the file I can share

 

[bosco_yip]

So this worked, but only for the first entry. The [IL.xlsx] data it's pulling from has the same "E2" value but different E & B columns per appearance of the "E2" value. So that's why the SMALL(IF( function was put in there

Working on making a mock up of the file I can share

{=INDEX([……..SMALL(IF……...,ROW($1:$1))………..

But your formula this part copy down also always return the first match.

Regards

 

[TheSeeker]

{=INDEX([……..SMALL(IF……...,ROW($1:$1))………..

But your formula this part copy down also always return the first match.

Regards

I change that for every next (to the right of) cell. So the =INDEX(SMALL(IF formula in A1 has ROW($1:$1) but the formula in B1 has ROW($2:$2) and so on

 

[TheSeeker]

See the below

Please read this.
http://chandoo.org/forum/threads/new-users-please-read.294/


One thing you can do to speed things up... Don't use entire column (ex. $C:$C).

Have a read of link below.
https://www.cogniview.com/articles-resources/speed-up-your-slow-spreadsheets

Alternately MS Query or PowerQuery will most often yield faster result (especially when list is not sorted on Key).

See attached files please!

The Test Book Contact List.xlsx is the one with the array formula and the Test IL.xlsx is the workbook the array formula is pulling from

 

[TheSeeker]

Just tested not using the entire column and it drastically sped up the processing time. Still a bit slow when it has to process the entire workbook, but sheet by sheet it's almost down to a fraction of a second

 

[Chihiro]

Good to hear.

If you need further help. It would be helpful if you have more than just one row of data.

 

[TheSeeker]

Good to hear.

If you need further help. It would be helpful if you have more than just one row of data.

I can create some more fake data so you can play around with it.

What I am really after is to replace the array functions with a non-array.

It's gotten faster by not using the entire column, but not as fast as I would like it to be.

Thank you!