VLOOKUP with numerous arguments

Leah Taylor

Hello!

I am trying to automate a status column but with look ups on two sheets. I have started the formula but I am wondering if it is possible to do this many arguments in one formula? Has anyone had any success at doing similar?

Blank = Assembly Job assigned to (is blank) sheet 1

Not Started = Assembly Job assigned to (is populated) and start date (is blank) sheet 1

In build bay = start date (is populated) sheet 1

In PD = Assembly complete (is checked) sheet 1

BUILD = PD complete (is checked) sheet 1

Broken down = breakdown complete (is checked) sheet 2

DOMESTIC = Ready for Dispatch (is checked) sheet 2

=IF(OR(VLOOKUP([Serial Number]@row, {Tow and Blow Builds Range 5}, 141, true) = 1), "Built", IF(OR(VLOOKUP([Serial Number]@row, {Tow and Blow Builds Range 1}, 32, true) = 1), "In PD", IF(VLOOKUP([Serial Number]@row, {Tow and Blow Builds Range 2}, 5, true) <> "", "In Build Bay")))

Paul Newcome

Are you able to provide some screenshots for context?

Leah Taylor

This screen shot is showing the status column that is getting populated and the serial number column that is the look up.

This is the sheet where the below comes from

Blank = Assembly Job assigned to (is blank) sheet 1

Not Started = Assembly Job assigned to (is populated) and start date (is blank) sheet 1

In build bay = start date (is populated) sheet 1

BUILD = PD complete (is checked) sheet 1

Broken down = breakdown complete (is checked)

DOMESTIC = Ready for Dispatch (is checked)

Thank you, i appreciate your time

Leah Taylor

Austin Smith

@Leah Taylor

I think you just need a cascading nested if function. Try something like (references not accurate b/c mult sheets):

=if(vlookup(serial, dispatch domestic serial to ready for dispatch, ready for dispatch col #)=1, "Domestic", if(vlookup(serial, tow and blow serial to breakdown, breakdown col)=1, "broken down", if(vlookup(serial......

You should be able to reference as many sheets as you want as long as you keep the most desirable (or most currently accurate) outcome first and nest toward least desirable (or least accurate as it may be).

Leah Taylor

Thank you @Austin Smith. These different outcomes are not ranked in the most desirable rather they are production stages so will go from one status to another as the sheet changes, will this still work?

Austin Smith

@Leah Taylor

It should work just fine. By most desirable, I didn't necessarily mean the one you would choose, but the last in the chain for a most positive outcome, which, in your case, is a deliverable product. Or, very clinically, just the end of the critical path.

You want the options to be set so that the end of the cycle is first, and gets stopped by the nested formulae as required.

Paul Newcome

My apologies for not getting back sooner. I agree with @Austin Smith that you should be able to use a series of nested IF statements. Think of it as working backwards with the last step coming first in your IFs and the first step coming last.

Nested IFs essentially assume that the first statement must be false if we are evaluating the second. This means you do not have to worry about using AND statements.

=IF(last is checked, "Last", IF(2nd to last is checked "2nd to last", so on and so forth....

When writing the second, you do not need to specify "if the last is not checked and the second to last is checked" because the "last is not checked" is already assumed to be true just by moving past it.

If you would prefer to work in the same order as the boxes would be checked, you would change your arguments to "not checked".

=IF(1st is not checked, "1st", IF(2nd is not checked, "2nd", so on and so forth.....

Depending on the complexity of the steps, this second method is sometimes easier to write.

Austin Smith

@Leah Taylor

@Paul Newcome explained that better. He's a legend around here.

Paul Newcome

@Austin Smith Haha. Thanks. I'm sure the company that makes my keyboards agrees.

Leah Taylor

Thank you both @Austin Smith and @Paul Newcome for your help. I really appreciate it.

I having a go with this one to start with, somethings not quite right?

=IF(VLOOKUP([Serial Number]@row ,{Dispatch - Domestic Range 1}, 32)=1, "Domestic"), IF(VLOOKUP([Serial Number]@ row, {Breakdown Check sheet Range 1}, 7)=1, "Broken down")

Paul Newcome

@Leah Taylor Try using "false" in the final portion of your VLOOKUPs. Leaving it out defaults to "true" which is an approximate match. "False" looks for an exact match. I also notice a couple of spaces in places where they don't belong.

=IF(VLOOKUP([Serial Number]@row, {Dispatch - Domestic Range 1}, 32, false)=1, "Domestic"), IF(VLOOKUP([Serial Number]@row, {Breakdown Check sheet Range 1}, 7, false)=1, "Broken down")