Basic Excel If Logic Question

**ffmariners** · 04-24-2010, 04:27 PM

I have a worksheet that I made for some manufacturing supervisors to use to plan their work out.

There are a few different machines that I have in a data validation drop down box in cell B9 (referenced below). They call to a table named "Standards" that has the machine names and default performance results we expect when we don't know the intricacies of a new product.

For each machine, I have a lookup table (with defined name nomenclature= _Mach#Standards, e.g. _124Standards). The lookup table gives us the different job names that run on each machine and their specific expected performance results.

On the worksheet, once the supervisor chooses the machine (Ex. 124) the next box below it in B11 uses the indirect function ("_"$B9) to list the job names which they can choose from the _124 table. Based on which job name they choose, the rest of the spreadsheet will populated expected instantaneous speeds, hourly targets, waste targets, etc.

However, if they do not choose a job name, I would like the formula to notice that B11 is blank and then do a vlookup of the machine in the default "Standards" table.

You can see my logic below using nested if statements. I thought that using the last argument "VLOOKUP(B9,Standards,2,FALSE)" would tell excel "If everything has been false thus far, use this equation." However, Excel still returns a #N/A.

Any ideas? Thanks!

=IF(B9=124,VLOOKUP($B$11,_124Standards,2,FALSE),IF(B9=125,VLOOKUP($B$11,_125Standards,2,FALSE),IF(B9=127,VLOOKUP($B$11,_127Standards,2,FALSE),IF(B9=131,VLOOKUP($B$11,_131Standards,2,FALSE),IF(B9=135,VLOOKUP($B$11,_135Standards,2,FALSE),VLOOKUP(B9,Standards,2,FALSE))))))

**helpplease!** · 04-24-2010, 06:47 PM

it is the vlookup that is returning #N/A

1. find out which vlookup is causing the #N/A
2. then manually check if the lookup_value is in the range, if not it will return #N/A
3. use wild cards if needed in the lookup_value i.e vlookup("*" & [cel] & "*",....,....,....)

**ffmariners** · 04-24-2010, 07:09 PM

Yeah, I know it is the VLOOKUP.

The formula returns the expected values as long as the very last VLOOKUP is not there. However, once it is added, it causes an issue.

If I do that VLOOKUP on it's own, it works. It has something to do with how I am viewing the IF logic I believe.

I tried to create another IF and just make it IF(ISBLANK(B11),VLOOKUP.... and IF(B11="",VLOOKUP...

Neither of those worked either... not sure why.

**daddylonglegs** · 04-24-2010, 07:31 PM

Yes, I think you need to check B11 first, does this work?

=IF($B$11="",VLOOKUP(B9,Standards,2,FALSE),VLOOKUP($B$11,INDIRECT("_"&B9&"Standards"),2,FALSE))

**ffmariners** · 04-24-2010, 08:27 PM

Originally Posted by daddylonglegs

Yes, I think you need to check B11 first, does this work?

=IF($B$11="",VLOOKUP(B9,Standards,2,FALSE),VLOOKUP($B$11,INDIRECT("_"&B9&"Standards"),2,FALSE))

Wow, that is much easier than 6 nested if statements... thanks so much!

**helpplease!** · 04-24-2010, 07:27 PM

the last vlookup is below:

VLOOKUP(B9,Standards,2,FALSE)

from your formula, it is saying that if B9 is not 124 or 125 or 127 or 131 or 135, then look for B9 in the table "Standards" and return the 2nd column. if this is not there then it will return #N/A

is that not what you expected? there is no mention of B11 if that is the case... if not then what did you want to happen if B9 is not in "Standards"?

**ffmariners** · 04-24-2010, 07:48 PM

Originally Posted by helpplease!

the last vlookup is below:

VLOOKUP(B9,Standards,2,FALSE)

from your formula, it is saying that if B9 is not 124 or 125 or 127 or 131 or 135, then look for B9 in the table "Standards" and return the 2nd column. if this is not there then it will return #N/A

is that not what you expected? there is no mention of B11 if that is the case... if not then what did you want to happen if B9 is not in "Standards"?

Yeah, you're right. The problem isn't with "VLOOKUP(B9,Standards,2,FALSE)" but... the problem is that it doesn't align with what I want. B9 HAS to be filled in with something, I am trying to make the last if statement look at whether or not B11 is filled in.

B9 will definitely be in standards... so really what I need it to do is:

If B11 is blank and B9 is 124, for example, to do a vlookup on 124 in the standards table. However, if B11 has JobX and B9 is 124, the vlookup should be based on JobX in the _124Standards table.

I see what I did wrong, now, just not sure how to fix!

**helpplease!** · 04-24-2010, 08:07 PM

"If B11 is blank and B9 is 124, for example, to do a vlookup on 124 in the standards table. "

i thought you said if B9 is 124 then look for B11 in _124Standards??

you have B9 and B11 and you have _124Standards, _125Standards,_127Standards, _131Standards, _135Standards and Standards.

think of all the permutations possible for B9 and B11 . what do you want to happen fr each one? you might need an extra if for each standard to check if B11 is blank...