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

Melisa Dannhauser

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!!!

Mark Cronk

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

Melisa Dannhauser

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...