Index Match problem with link to another file

asad

Well-known Member
Joined
Sep 9, 2008
Messages
1,421
Hello Gurus,

I got this formula:
IF((TODAY()+7-WEEKDAY(TODAY(),2)) < O$2,"",COUNT(IF(INDEX('[Footscray Roster 15th May 2011TrialAli.xls]Weekly Roster'!$E$4:$AJ$200,MATCH($B5,'[Footscray Roster 15th May 2011TrialAli.xls]Weekly Roster'!$E$4:$E$200,0),4):INDEX('[Footscray Roster 15th May 2011TrialAli.xls]Weekly Roster'!$E$4:$AJ$200,MATCH($B5,'[Footscray Roster 15th May 2011TrialAli.xls]Weekly Roster'!$E$4:$E$200,0),32)="RDO",1,"")))

The answer should be 1. But it is giving result = 4.
It is supposed to look for "RDO" in the row with the person's name in column E and give me the result if there is one in any of the cells in that row between columns H and AJ. There will never be more than 1 "RDO" in any row. So why is giving me 4 as answer?
Can somebody tell me why is it doing this?
Is there another of solving this problem?
 
Last edited:

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

asad

Well-known Member
Joined
Sep 9, 2008
Messages
1,421
And, this particular formula should return blank as there is no RDO for this person in that row. But it is returning 4.
 

asad

Well-known Member
Joined
Sep 9, 2008
Messages
1,421
Don't worry guys. My mistake. The original file has wrong data in it. That was causing the problem.
Sorry for wasting your time.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,725
Messages
5,524,475
Members
409,583
Latest member
RedHelp

This Week's Hot Topics

Top