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

BestiaFPV

New Member
Joined
Dec 21, 2016
Messages
13
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
The second one is adding a 0 instead

Any help would be useful

Thanks
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Easy way to answer that
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

JCTmoS73w0S_v2abIT71dLPD6SvR0fFDlLVlq9U-920
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
=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:
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,194
Members
448,554
Latest member
Gleisner2

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