Nested Formula

Hannah H

I’m new to posting in the Community. I have looked through previous posts but cannot find anything similar to what I am trying to do. I am trying to nest a LOOKUP formula.

 

My current (working) formula is:

=LOOKUP("In Progress", [Progress Should Be]74:[Unit Number]154, 4, true)

 

There are three possible values in the Progress Should Be column - “In Progress”, “In Past”, “In Future”.

 

I would like my formula to lookup “In Progress” and if this is not found, to lookup “In Past” and “In Future” and return which one of these two are present.

 

Is this possible?

Robert S.

Hello Hannah,

 

Thanks for the question, and welcome to the Community. There's a few possible ways to do what it sounds like you're looking for, however there's more information on exactly how you want this to behave that I'd need in order to give you more specifics.

If I'm understanding correctly, you want the formula to return the value shown in the "Unit Number" column for the first row (starting from the top) that has "In Progress" in the "Progress Should Be" column. What you'd like to happen if no cells with "In Progress" are found here is where I'm unsure. I'm thinking one of the following options could be what you're looking for, however I could be wrong so please let me know:

 

Option A:

If the formula doesn't find any cells with "In Progress" in that column within the given range, the formula returns the value shown in the "Unit Number" column for the first row that has "In Past" in the that column. Then the same thing happens for "In Future" if there are no "In Past" cells found, and the formula leaved the cell blank if none are found. If this is what you're looking for, here's an example of how that formula could be written using the IFERROR function:

 

=IFERROR(VLOOKUP("In Progress", [Progress Should Be]74:[Unit Number]154, 4, true), IFERROR(VLOOKUP("In Past", [Progress Should Be]74:[Unit Number]154, 4, true), IFERROR(VLOOKUP("In Future", [Progress Should Be]74:[Unit Number]154, 4, true), "")))

 

More on the IFERROR function can be found here if needed (https://help.smartsheet.com/function/iferror).

 

Option B:

If the formula doesn't find any cells with "In Progress" in that column within the given range, it returns "In Past" if there are any cells with "In Past" found in the "Progress Should Be" column, or it returns "In Future" if there are any cells with "In Future" found in the "Progress Should Be" column. If there are both "In Past" and "In Future" cells found, you would need to specify what to return or if one should take precedence over the other e.g. if both are found then return "In Past", or if both are found then return "In Past/In Future".

 

With the added information on how you want this to work, I'm happy to help give more specifics on how to build this formula.

Hannah H

Hi Robert,

 

Thank you for tackling my problem!

 

Option B is what I am looking for, with In Future taking precedence over In Past.

 

Hannah

Robert S.

Hi Hannah,

 

I'm happy to help. Option B would work similarly to Option A's formula, however you would replace the second and third IFERRORs with nested IFs instead. Here's an example of how this could be written:

 

=IFERROR(VLOOKUP("In Progress", [Progress Should Be]74:[Unit Number]154, 4, true), IF(COUNTIF([Progress Should Be]74:[Progress Should Be]154, "In Future") > 0, "In Future", IF(COUNTIF([Progress Should Be]74:[Progress Should Be]154, "In Past") > 0, "In Past", "")))

 

Since nested formulas work from left to right and stop at the first true statement, you want to put them in the order of importance. Since "In Future" takes precedence over "In Past", I've put it's statement before the "In Past" statement. This IF statement works by counting the number of cells in the given range that have a value of "In Future" using the COUNTIF function, and if that number is greater than 0 that means there's at least 1 cell in that range with that value so it returns "In Future". If that count is not greater than 0, then it moves on and does the same thing for "In Past". 

 

I hope this helps to explain it all, but please let me know if you have any further questions on this and I'll be happy to help.

Hannah H

Hi Robert,

This formula worked a treat! Thank you for your clear breakdown and explanation of how the formula works as well; it's really helpful to develop understanding of these formulas.

Hannah