EXCEL Formula Help Required

[Karen2403]

Hi - I have 4 formulas that work separately but when I try to make it in to one formula, I keep getting an error message - can anyone help please? I am quite new to these detailed formulas so really not sure where I am going wrong.

C3 is a drop down box where the user will select either CD, ED, EM, MM, SEE, SE, SPE or ST, and then depending on what is selected will return the matching name from the relevant column where there is not a blank (I hope I have explained this correctly).

=IF(C3="CD", INDEX(Employee_Data[Full Name],SMALL(IF(Employee_Data[Date]<>"",ROW(Employee_Data[Full Name])-ROW(INDEX(Employee_Data[Full Name],1,1))+1), ROWS($E$2:E2))),IF(C3="ED", INDEX(Employee_Data[Full Name],SMALL(IF(Employee_Data[Date2]<>"",ROW(Employee_Data[Full Name])-ROW(INDEX(Employee_Data[Full Name],1,1))+1), ROWS($E$2:E2)))))

=IF(C3="EM", INDEX(Employee_Data[Full Name],SMALL(IF(Employee_Data[Date3]<>"",ROW(Employee_Data[Full Name])-ROW(INDEX(Employee_Data[Full Name],1,1))+1), ROWS($E$2:E2))),IF(C3="MM", INDEX(Employee_Data[Full Name],SMALL(IF(Employee_Data[Date4]<>"",ROW(Employee_Data[Full Name])-ROW(INDEX(Employee_Data[Full Name],1,1))+1), ROWS($E$2:E2)))))

=IF(C3="SEE", INDEX(Employee_Data[Full Name],SMALL(IF(Employee_Data[Date5]<>"",ROW(Employee_Data[Full Name])-ROW(INDEX(Employee_Data[Full Name],1,1))+1), ROWS($E$2:E2))),IF(C3="SE", INDEX(Employee_Data[Full Name],SMALL(IF(Employee_Data[Date6]<>"",ROW(Employee_Data[Full Name])-ROW(INDEX(Employee_Data[Full Name],1,1))+1), ROWS($E$2:E2)))))

=IF(C3="SPE", INDEX(Employee_Data[Full Name],SMALL(IF(Employee_Data[Date7]<>"",ROW(Employee_Data[Full Name])-ROW(INDEX(Employee_Data[Full Name],1,1))+1), ROWS($E$2:E2))),IF(C3="ST", INDEX(Employee_Data[Full Name],SMALL(IF(Employee_Data[Date8]<>"",ROW(Employee_Data[Full Name])-ROW(INDEX(Employee_Data[Full Name],1,1))+1), ROWS($E$2:E2)))))

Many Thanks

 

[GraH]

Hi,

No in favour of such formula constructs, and with 2016 you would have IFS on boards (makes it slightly easier), but without seeing the data it is hard to suggest a better approach. So I went with your formulas.
I'm guessing you have troubles with the position of the parentheses.
Please try

=IF(C3="CD", 
    INDEX(Employee_Data[Full Name],SMALL(IF(Employee_Data[Date]<>"",ROW(Employee_Data[Full Name])-ROW(INDEX(Employee_Data[Full Name],1,1))+1), ROWS($E$2:E2))),
 IF(C3="ED", 
    INDEX(Employee_Data[Full Name],SMALL(IF(Employee_Data[Date2]<>"",ROW(Employee_Data[Full Name])-ROW(INDEX(Employee_Data[Full Name],1,1))+1), ROWS($E$2:E2)))
 ,
 IF(C3="EM", 
    INDEX(Employee_Data[Full Name],SMALL(IF(Employee_Data[Date3]<>"",ROW(Employee_Data[Full Name])-ROW(INDEX(Employee_Data[Full Name],1,1))+1), ROWS($E$2:E2))),
 IF(C3="MM", INDEX(Employee_Data[Full Name],SMALL(IF(Employee_Data[Date4]<>"",ROW(Employee_Data[Full Name])-ROW(INDEX(Employee_Data[Full Name],1,1))+1), ROWS($E$2:E2))))
 ,            
 IF(C3="SEE", 
    INDEX(Employee_Data[Full Name],SMALL(IF(Employee_Data[Date5]<>"",ROW(Employee_Data[Full Name])-ROW(INDEX(Employee_Data[Full Name],1,1))+1), ROWS($E$2:E2))),
 IF(C3="SE", INDEX(Employee_Data[Full Name],SMALL(IF(Employee_Data[Date6]<>"",ROW(Employee_Data[Full Name])-ROW(INDEX(Employee_Data[Full Name],1,1))+1), ROWS($E$2:E2)))    
    ,
IF(C3="SPE",
    INDEX(Employee_Data[Full Name],SMALL(IF(Employee_Data[Date7]<>"",ROW(Employee_Data[Full Name])-ROW(INDEX(Employee_Data[Full Name],1,1))+1), ROWS($E$2:E2))),
IF(C3="ST", INDEX(Employee_Data[Full Name],SMALL(IF(Employee_Data[Date8]<>"",ROW(Employee_Data[Full Name])-ROW(INDEX(Employee_Data[Full Name],1,1))+1), ROWS($E$2:E2)))))            
)))))

 

[Karen2403]

Hi,

No in favour of such formula constructs, and with 2016 you would have IFS on boards (makes it slightly easier), but without seeing the data it is hard to suggest a better approach. So I went with your formulas.
I'm guessing you have troubles with the position of the parentheses.
Please try

=IF(C3="CD",
    INDEX(Employee_Data[Full Name],SMALL(IF(Employee_Data[Date]<>"",ROW(Employee_Data[Full Name])-ROW(INDEX(Employee_Data[Full Name],1,1))+1), ROWS($E$2:E2))),
 IF(C3="ED",
    INDEX(Employee_Data[Full Name],SMALL(IF(Employee_Data[Date2]<>"",ROW(Employee_Data[Full Name])-ROW(INDEX(Employee_Data[Full Name],1,1))+1), ROWS($E$2:E2)))
 ,
 IF(C3="EM",
    INDEX(Employee_Data[Full Name],SMALL(IF(Employee_Data[Date3]<>"",ROW(Employee_Data[Full Name])-ROW(INDEX(Employee_Data[Full Name],1,1))+1), ROWS($E$2:E2))),
 IF(C3="MM", INDEX(Employee_Data[Full Name],SMALL(IF(Employee_Data[Date4]<>"",ROW(Employee_Data[Full Name])-ROW(INDEX(Employee_Data[Full Name],1,1))+1), ROWS($E$2:E2))))
 ,           
 IF(C3="SEE",
    INDEX(Employee_Data[Full Name],SMALL(IF(Employee_Data[Date5]<>"",ROW(Employee_Data[Full Name])-ROW(INDEX(Employee_Data[Full Name],1,1))+1), ROWS($E$2:E2))),
 IF(C3="SE", INDEX(Employee_Data[Full Name],SMALL(IF(Employee_Data[Date6]<>"",ROW(Employee_Data[Full Name])-ROW(INDEX(Employee_Data[Full Name],1,1))+1), ROWS($E$2:E2)))   
    ,
IF(C3="SPE",
    INDEX(Employee_Data[Full Name],SMALL(IF(Employee_Data[Date7]<>"",ROW(Employee_Data[Full Name])-ROW(INDEX(Employee_Data[Full Name],1,1))+1), ROWS($E$2:E2))),
IF(C3="ST", INDEX(Employee_Data[Full Name],SMALL(IF(Employee_Data[Date8]<>"",ROW(Employee_Data[Full Name])-ROW(INDEX(Employee_Data[Full Name],1,1))+1), ROWS($E$2:E2)))))           
)))))

Thank you for this - I have tried it and get the same error message when I tired - the error message states 'you've entered too many arguments for this function'

 

[GraH]

Probably I'm in trouble too with the parenthesis ?‍ (without Excel, it's difficult for me).
Use the formula intellisence. And copy paste each portion of the formula in the correct field and build it up piece by piece.
Where you have false a new IF starts. At the very end close all IFs made.

I think I miscounted in the fourth IF where I had 4 closing parenthesis, there should be only 3. And thus 1 extra at the end.

=IF(C3="CD", 
    INDEX(Employee_Data[Full Name],SMALL(IF(Employee_Data[Date]<>"",ROW(Employee_Data[Full Name])-ROW(INDEX(Employee_Data[Full Name],1,1))+1), ROWS($E$2:E2))),
 IF(C3="ED", 
    INDEX(Employee_Data[Full Name],SMALL(IF(Employee_Data[Date2]<>"",ROW(Employee_Data[Full Name])-ROW(INDEX(Employee_Data[Full Name],1,1))+1), ROWS($E$2:E2)))
    ,
 IF(C3="EM", 
    INDEX(Employee_Data[Full Name],SMALL(IF(Employee_Data[Date3]<>"",ROW(Employee_Data[Full Name])-ROW(INDEX(Employee_Data[Full Name],1,1))+1), ROWS($E$2:E2))),
 IF(C3="MM", INDEX(Employee_Data[Full Name],SMALL(IF(Employee_Data[Date4]<>"",ROW(Employee_Data[Full Name])-ROW(INDEX(Employee_Data[Full Name],1,1))+1), ROWS($E$2:E2)))
    ,            
 IF(C3="SEE", 
    INDEX(Employee_Data[Full Name],SMALL(IF(Employee_Data[Date5]<>"",ROW(Employee_Data[Full Name])-ROW(INDEX(Employee_Data[Full Name],1,1))+1), ROWS($E$2:E2))),
 IF(C3="SE", INDEX(Employee_Data[Full Name],SMALL(IF(Employee_Data[Date6]<>"",ROW(Employee_Data[Full Name])-ROW(INDEX(Employee_Data[Full Name],1,1))+1), ROWS($E$2:E2)))    
    ,
IF(C3="SPE",
    INDEX(Employee_Data[Full Name],SMALL(IF(Employee_Data[Date7]<>"",ROW(Employee_Data[Full Name])-ROW(INDEX(Employee_Data[Full Name],1,1))+1), ROWS($E$2:E2))),
IF(C3="ST", INDEX(Employee_Data[Full Name],SMALL(IF(Employee_Data[Date8]<>"",ROW(Employee_Data[Full Name])-ROW(INDEX(Employee_Data[Full Name],1,1))+1), ROWS($E$2:E2)))))            
))))))

 

[Fluff]

Probably I'm in trouble too with the parenthesis

Just the one of them out of place.

try

=IF(C3="CD",
    INDEX(Employee_Data[Full name],SMALL(IF(Employee_Data[Date]<>"",ROW(Employee_Data[Full name])-ROW(INDEX(Employee_Data[Full name],1,1))+1), ROWS($E$2:E2))),
 IF(C3="ED",
    INDEX(Employee_Data[Full name],SMALL(IF(Employee_Data[Date2]<>"",ROW(Employee_Data[Full name])-ROW(INDEX(Employee_Data[Full name],1,1))+1), ROWS($E$2:E2))),
 IF(C3="EM",
    INDEX(Employee_Data[Full name],SMALL(IF(Employee_Data[Date3]<>"",ROW(Employee_Data[Full name])-ROW(INDEX(Employee_Data[Full name],1,1))+1), ROWS($E$2:E2))),
 IF(C3="MM",
    INDEX(Employee_Data[Full name],SMALL(IF(Employee_Data[Date4]<>"",ROW(Employee_Data[Full name])-ROW(INDEX(Employee_Data[Full name],1,1))+1), ROWS($E$2:E2))),
 IF(C3="SEE",
    INDEX(Employee_Data[Full name],SMALL(IF(Employee_Data[Date5]<>"",ROW(Employee_Data[Full name])-ROW(INDEX(Employee_Data[Full name],1,1))+1), ROWS($E$2:E2))),
 IF(C3="SE",
   INDEX(Employee_Data[Full name],SMALL(IF(Employee_Data[Date6]<>"",ROW(Employee_Data[Full name])-ROW(INDEX(Employee_Data[Full name],1,1))+1), ROWS($E$2:E2))),
IF(C3="SPE",
    INDEX(Employee_Data[Full name],SMALL(IF(Employee_Data[Date7]<>"",ROW(Employee_Data[Full name])-ROW(INDEX(Employee_Data[Full name],1,1))+1), ROWS($E$2:E2))),
IF(C3="ST",
    INDEX(Employee_Data[Full name],SMALL(IF(Employee_Data[Date8]<>"",ROW(Employee_Data[Full name])-ROW(INDEX(Employee_Data[Full name],1,1))+1), ROWS($E$2:E2)))))
))))))

 

[GraH]

Thanks @Fluff, I spotted it too

 

[Karen2403]

Oh wow!! Thank you so much - it works perfectly . I think I will take a tip from that on how you have structured the writing of the query - it makes it much easier to read - not that I am sure I still would have sussed it out - I do get confused on the number of parenthesis required! Thank you again for your time and help!!

 

[GraH]

Making it readable is a best practice indeed. Glad we could help. Not that difficult but still braininjury sensitieve with those parantheses.
I do get confused on the number of parenthesis required! Thank you again for your time and help!!
For every that opens one needs to close..