Help with IF and INDEX COLLECT

Ronald Anderson

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)

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

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