Array formula not working in current workbook - works fine in previous versions

[mattstopel]

Hi All,

I am having a real frustration at the moment with one of my workbooks.

I have an array formula, nothing too complex that won't return any results.

In my previous version, V5, it works flawlessly, in V6, no good at all. Formula is the exact same between both worksheets. Named arrays are still the same also.

Formula is =IFERROR(INDEX(EntryID,LARGE(IF(Entryunit=$D$6,ROW(EntryID)),ROW(1:1))),"") and entered as CTRL+SHIRT+ENTER.

Any ideas accepted!!

TIA

 

[AhoyNC]

Remove the IFERROR part of your formula to see what error if any is being produced.

 

[mattstopel]

@AhoyNC

This returned a #value error.

error in value - a value used in the formula is of the wrong data type? Cells are formatted as D6 is formatted as Text, but General doesn't change this?


Just very strange it works in a previous sheet and only difference is new data has been added? I have ensure the EntryID and Entryunit are formatted as general.

 

[AhoyNC]

No, changing the format of a cell will not change a number enter as TEXT to NUMERIC.

If you highlight the cell or cells and see an ! (exclamation mark) as below.

Click on it and choose "Convert to Number"

 

[mattstopel]

unfortunately no luck. Just filtered through the 8,000 sets of data and no issues. The adjacent column has some errors but they are not involved with the issue formula.

 

[AhoyNC]

What if you remove the new data, Does the error go away?

 

[jmcdaid]

Are you on Office 365? If so, does your Excel have Dynamic Arrays? You can tell by entered this formula into a blank cell =SEQUENCE(10). IF you do, then I believe your formula can be simplified.

 

[mattstopel]

10

HI jmcdaid,

it returns 1, so i assume it does have Dynamic Arrays???

If so, keen for help, this has been doing my head in. I have a similar sheet that looks up another column and returns without errors.

Advice greatly appreciated.

Regards

 

[jmcdaid]

HI jmcdaid,

it returns 1, so i assume it does have Dynamic Arrays???

If so, keen for help, this has been doing my head in. I have a similar sheet that looks up another column and returns without errors.

Advice greatly appreciated.

Regards

Just 1? Or does it return 1, 2, 3, ..., 10 across multiple cells?

p.s. sorry about the delay - I missed your reply.