Function for looking up data in a range and display "yes" or "no"

Options
Melisa Dannhauser
Melisa Dannhauser ✭✭✭
edited 06/17/21 in Formulas and Functions

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

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Options

    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.

  • Melisa Dannhauser
    Options

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!