Rid #ref error in a Index Match Formula

Below is a table for inputting , I need the output as the table below and have used a formula which is producing the #ref error, please either help me correct the formula or get an eaiser formula – I have searched the internet.This is the formula I am using         

=INDEX(Sheet1!$A$4:$A$98;SMALL(INDEX((Sheet1!$A$112=Sheet1!$C$4:$C$98)*MATCH(ROW(Sheet1!$C$4:$C$98);ROW(Sheet1!$C$4:$C$98))+(Sheet1!$A$112<>Sheet1!$C$4:$C$98)*1048577;0;0);ROW($R140)))                  
                     
    1-4 9-11 16-18            
Bakker Hilde                  
Baldie Carole Nyth  X X            
Baldie Gavin Nyth  X X            
Bergins Ernest ADDO                
Bester Dieter SHR                
Bezuidenhout Elmarie SHR                
Bezuidenhout Wynand ADDO                
Bolton Laura SHR                
Bolton Nikki ADDO                
Bolton Wayne ADDO                
Bosman Nicky ADDO                
Bosman Wayne NC                
Bouwer Judy NC                
                     
                     
                     
                     
29-Aug Addo Nyathi Colchester Darlington
Accom(SHR) Accom Camp Wendy Camp House 1 House 2 Room 1 Room 2 Room 3
January
1-4 Bester Bergins #NUM! Baldie Bosman #NUM! #NUM! #NUM! #NUM! #NUM!
Bezuidenhout Bezuidenhout #NUM! Baldie Bouwer #NUM! #NUM!      
Bolton Bolton #NUM! #NUM! #NUM!          
#N/A Bolton #NUM! #NUM! #NUM!          
#N/A Bosman #NUM! #NUM! #NUM!          

By: Pat McMaster

Leave a Reply

Your email address will not be published. Required fields are marked *