Function problem: Function correct but not running

**helterskelter101** · 09-02-2018, 05:00 AM

Hi! I need to match data from two excell sheets. To achieve this I wrote the following function:
=INDEX(Opnames!$F:$F;MATCH(1;INDEX((F6<=Opnames!$F:$F)*(G6>=Opnames!$E:$E)*(B6=Opnames!$A:$A);0);0))

This function works, buttttttttt...
When I run it for an individual data entry there are no problems. It either gives me the data I'm looking for or NA when there is no available match (this is a likely outcome for many data entries). But once I run the function for all my data entries (25000 or so) it only returns NA's... When I manually execute the function there are no problems, but running it like this doesn't work. Does anybody have a clue how I can fix this?
I added an example down below.

Cheers!

**jason.b75** · 09-02-2018, 05:15 AM

Thanks for updating your thread title

**FDibbins** · 09-03-2018, 01:37 AM

Looks like that is an ARRAY formula ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

**romperstomper** · 09-03-2018, 04:05 AM

The second INDEX negates the need for array entry.

The formula results look correct for the data in that file. Can you indicate one that you think should return something but doesn't? (Also, are you sure you have automatic calculation set?)

**jason.b75** · 09-03-2018, 10:56 AM

Just to point out, N/A and #N/A are not the same thing.

#N/A is the formula returning an error, not returning one of the N/A's from the other sheet. This can sometimes cause a knock on effect, one error creates another where a result might be expected, although I can't see anything to suggest that is happening here.

Also, whole columns in array formulas

Even though the formula is not CSE confirmed, the nature of it is still an array, calculating over 1 million empty rows!

Try formatting the data in the Opnames sheet as a table, then use this formula in J2 instead.

=INDEX(Table1[Opname 12],MATCH(1,INDEX(($F2<=INDEX(Table1[[Opname 1]:[Opname 78]],COLUMNS($J2:J2)))*($G2>=INDEX(Table1[[Opname 1]:[Opname 78]],COLUMNS($J2:J2)))*($B2=Table1[UserName]),0),0))

It gives the same results as yours, just more efficiently.

To help you get the results that you want, please refer to rorya's post above.

**helterskelter101** · 09-04-2018, 10:17 AM

Originally Posted by jason.b75

Just to point out, N/A and #N/A are not the same thing.

#N/A is the formula returning an error, not returning one of the N/A's from the other sheet. This can sometimes cause a knock on effect, one error creates another where a result might be expected, although I can't see anything to suggest that is happening here.

I added the NA's later in the opnames tab, they weren't there before but it returend the same

**helterskelter101** · 09-04-2018, 10:07 AM

Hi guys! I'm gonna try to implement your suggestions today

Thanks for the advice and I will let you know if I get any results. I'm a bit of a rookie with regards to this so thanks!

Function problem: Function correct but not running

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Function problem: Function correct but not running

Re: Function problem

Re: Function problem: Function correct but not running

Re: Function problem: Function correct but not running

Re: Function problem: Function correct but not running

Re: Function problem: Function correct but not running

Re: Function problem: Function correct but not running

Thread Information

Users Browsing this Thread

Similar Threads

[VBA] Problem with outmail function - function sends mails only to 1 recepient

Excel Problem with worksheet, need to know which function for penny a day doubled problem

Problem Inserting Round function into an IF function

[SOLVED] problem with 'IF' function

IF function problem

[SOLVED] IF function Problem

if Function problem

Bookmarks

Bookmarks

Posting Permissions