# Index formula is not working the same in 2 different locations on the same sheet

#### BestiaFPV

##### New Member
I have the following index formula in my sheet twice

Once at D59 =IF('Pilot List'!\$G\$28>=1,IFNA(INDEX(\$A\$4:\$C\$51,MATCH(0,COUNTIF(\$D\$58:\$D58,\$B\$4:\$B\$51)+(\$C\$4:\$C\$51<>\$E\$57),0),COLUMN(B4)),"Empty"),"") and then again D86 =IF('Pilot List'!\$J\$28>=1,IFNA(INDEX(\$A\$4:\$C\$51,MATCH(0,COUNTIF(\$D\$85:\$D85,\$B\$4:\$B\$51)+(\$C\$4:\$C\$51<>\$E\$84),0),COLUMN(B4)),"Empty"),"")

They are suppose to do the same thing, just use a different Cell to get the reference

The first one is working, but the second is doing weird things

It is a big book, bouncing across a few different sheets - so here is a link to download it - https://www.dropbox.com/s/l4biughyyaopfjv/WCMRC-BuzzCup.xlsm?dl=0

The aim is to look up the name, and if there is a number that isnt dividable by 4, to type "Empty"
The first set is working perfectly

Any help would be useful

Thanks

### Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

#### AliGW

##### Banned
What are your expected outcomes in the second range?

#### BestiaFPV

##### New Member
If there is a pilots name with B next to it, it should be them.
If the total of names is less then dividable by 4 Empty
If the total names is dividable by 4, nothing

Here is the working example, First 3 are names, 4th is Empty (because there is only 3 names) and everything is blank underneath it

#### BestiaFPV

##### New Member
But instead of being class A ... it should be for Class B

#### AliGW

##### Banned
I understand the calculation. What I am asking is what are YOUR expected outcomes in the second range? What SHOULD be there instead of what is there at the moment? Which three names should appear?

#### BestiaFPV

##### New Member

So this is the Pilots names

What I am expecting is A,B,C,Empty

What I am getting is A,B,C,0

#### AliGW

##### Banned
If you run the formula through evaluate formula, you will see that it does not resolve to #N/A, which is why it is returning 0 instead of empty. In the first array, the last item is resolving to #N/A.

#### BestiaFPV

##### New Member
Ok ... So how do I fix it?

#### AliGW

##### Banned
As far as I can see, it isn't doing anything wrong. Could you talk us through the calculation that you think should resolve to Empty in that cell?

Last edited:

#### BestiaFPV

##### New Member
=IF('Pilot List'!\$J\$28>=1,IFNA(INDEX(\$A\$4:\$C\$51,MATCH(0,COUNTIF(\$D\$85:\$D88,\$B\$4:\$B\$51)+(\$C\$4:\$C\$51<>\$E\$84),0),COLUMN(B7)),"Empty"),"")

IF('Pilot List'!\$J\$28>=1 - Checks if the cell equals or is greater then 1 if not it posts ""

IFNA - Checks for an NA error if there is an NA error, it replaces the result with "Empty"

INDEX(\$A\$4:\$C\$51,MATCH(0,COUNTIF(\$D\$85:\$D88,\$B\$4:\$B\$51)+(\$C\$4:\$C\$51<>\$E\$84) - looks up the name, if it cant find one, it should return #N/A

The annoying part is, that I took the formula which is working from higher up in the same sheet, changed the reference points (B7, E84 and 'Pilot List'!\$J\$28) so I cant understand why it isnt working

Last edited:

Replies
4
Views
226
Replies
3
Views
2K
Replies
2
Views
409
Replies
3
Views
2K
Replies
0
Views
588

1,195,833
Messages
6,011,853
Members
441,650
Latest member
ceyoung75

### 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.

### Which adblocker are you using?

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

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