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
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives