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

  • Hollie Green
    Hollie Green ✭✭✭✭✭✭

    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")

  • Ronald Anderson
    Ronald Anderson ✭✭✭✭

    Thank you Hollie. When I used your formula I received a incorrect argument error.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!