Can I use a VLOOK within an if statement?
I want to use a VLOOK up put only if there is a balance in a column:
=IF([Request Infrastructure]@row = 0, 0, VLOOKUP($Project@row, {2022 Nkhoma Hospital Project Plan Range 1}, 14, false))
If my Request Infrastructure is zero I want 0 to be in the Available Balance Infrastructure column, if it is a number I want to perform the above VLookup. With the formula above, it is performing the look up no matter what is in the Request Infrastructure column.
As you can see in the photo my "available balance infrastructure" is always pulling from the VLOOKUP but I only want it to pull a number if there is a number in the "Request Infrastructure" column
Best Answer
-
Your formula is checking if it is =0 (which the blanks will return as false).
You can change it to <>"":
=IF([Request Infrastructure]@row <>"", 0, VLOOKUP($Project@row, {2022 Nkhoma Hospital Project Plan Range 1}, 14, false))
Alternatively, you can add both with an OR function:
=IF(or([Request Infrastructure]@row <>"",[Request Infrastructure]@row=0), 0, VLOOKUP($Project@row, {2022 Nkhoma Hospital Project Plan Range 1}, 14, false))
Answers
-
Your formula is checking if it is =0 (which the blanks will return as false).
You can change it to <>"":
=IF([Request Infrastructure]@row <>"", 0, VLOOKUP($Project@row, {2022 Nkhoma Hospital Project Plan Range 1}, 14, false))
Alternatively, you can add both with an OR function:
=IF(or([Request Infrastructure]@row <>"",[Request Infrastructure]@row=0), 0, VLOOKUP($Project@row, {2022 Nkhoma Hospital Project Plan Range 1}, 14, false))
-
Thank you, the above did the opposite of what I was looking for but I changed <> to = and worked perfectly!
-
My mistake. That would be correct.
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives