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
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 443 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 290 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!