Function for looking up data in a range and display "yes" or "no"
I'm trying to pull some data and can't figure out which formula to use. Here's what I need:
I have a unique ID in my master database. In this same database, I also have a Budget 1, Budget 2, Budget 3 (the budgets are each in a separate column) for each unique ID - see example screenshot (first 4 columns).
I am now pulling those IDs into a separate sheet and need to figure out the following:
Look up the unique ID and display "yes" if that ID has all 3 budgets populated in the master database. If one of the 3 budgets is blank, display "no". What I am trying to accomplish is highlighted in yellow in my screenshot.
Any input how I can do this? I would rather not use vlookup since the master sheet is ever changing and column order changes all the time!
Thank you in advance!!!
Answers
-
Hi Melissa,
If you can add a helper column to your master db that will be the easiest solution. Add a column [result] with the column formula:
=IF(OR(ISBLANK([budget 1]@row), ISBLANK([budget 2]@row), ISBLANK([budget 3]@row)), "No", "Yes")
The use a VLOOKUP or Index/match to pull the result into your results page. That formula would be: =VLOOKUP([ID-Analysis]@row, {external range to db}, 5, False) where the external range has your ID in the left most column and includes the results column. Change the 5nto be the column count from ID to Result.
Work for you?
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
The problem is I have several groups like this so I would need many helper columns. I would like to avoid it. Also, vlookup is less than ideal in my case because the sheet gets re-arranged and changed all the time... I found a workaround for now but would really love to see if there's an option to wrap this into one formula, even if it's a big one...
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!