Help with IF and INDEX COLLECT
I need to collect information from two different sheets based on a cell value in another sheet.
I have created the formula below, but it doesn't seem to work completely. I get an invalid value error if the employee number is not on the lookup sheets.
Thanks!
=IF([Airside / Landside]@row = "Airside", INDEX(COLLECT({EqNo}, {Employee ID}, [Employee #]@row), 1), IF([Airside / Landside]@row = "Landside", INDEX(COLLECT({LSEQNo}, {LS Employee Number}, [Employee #]@row), 1)
Answers
-
If the error is only occurring when they are not on the sheet add an if error to the formula
=IFERROR(IF([Airside / Landside]@row = "Airside", INDEX(COLLECT({EqNo}, {Employee ID}, [Employee #]@row), 1), IF([Airside / Landside]@row = "Landside", INDEX(COLLECT({LSEQNo}, {LS Employee Number}, [Employee #]@row), 1),"put what you want it to be when the employee is not on the sheet")
-
Thank you Hollie. When I used your formula I received a incorrect argument error.
Help Article Resources
Categories
Check out the Formula Handbook template!