Sunday, January 15, 2012

MS Excel: Find a value in one list in another list and do two different things if found

I use the following formula or variations on it pretty regularly, but always have to double check the logic to make sure I'm not creating a mess.  Here's my notes to myself on what it does.

=IF(ISERROR((VLOOKUP([what to look for],[where to look for it],1,FALSE))),[do something if TRUE],[do something else if FALSE])

A specific example of when I used it last is:
=IF(ISERROR((VLOOKUP($F3,$B$3:$B$1214,1,FALSE))),F3,"Shipped")
Basically, I have a long list of all the parts that need to be shipped out.  These are all in column F.  I also have a shorter list of parts that have already been shipped.  These are in column B.  Now I need to find what parts still need to be shipped.  I imagine I need to take all the parts in F and try to find them in the shipped parts B.  If they are in that list, then they're shipped and I'm just going to fill the cell with "Shipped".  If they aren't in the list, they haven't been shipped, so I'll fill in the cell with the part.

The whole thing works from the inside out, so the first thing the function does is:

(VLOOKUP($F3,$B$3:$B$1214,1,FALSE)
This takes the value found in F3, and looks for it in column B (or specifically B3 through B1214).
If it finds the F3 value in B, it returns F3,
If it doesn't find F3, it returns #N/A.

Then:

(ISERROR()
This checks to see if something is an error equal to #N/A, #VALUE, #DIV/0, etc.
If it's an error, then ISERROR returns TRUE.
If it's not an error, then ISERROR returns FALSE.

Then:

IF([the TEST],[something if the test is TRUE], [something if the test is FALSE])
If tests a condition (i.e. ) is TRUE or FALSE.
If the test is TRUE, then the thing after the test in the function happens.
If the test is FALSE, then the last thing in the function happens.

So...

First, when we do a (VLOOKUP($F3,$B$3:$B$1214,1,FALSE), maybe we get the value of F3, or maybe we get an error like #N/A.

Let's just assume F3 isn't found.

If so, (VLOOKUP($F3,$B$3:$B$1214,1,FALSE) would return a #N/A.

Next, consequently, the (ISERROR((VLOOKUP($F3,$B$3:$B$1214,1,FALSE))) portion of the function ends up logically looking like (ISERROR(#N/A).

Since ISERROR checks for a value of #N/A, the ISERROR portion of the function returns TRUE.

Ultimately, this first means the IF(ISERROR((VLOOKUP($F3,$B$3:$B$1214,1,FALSE))),F3,"Shipped") logically looks like IF(ISERROR(#N/A),F3,"Shipped") after the VLOOKUP fails to find F3. Then the IF(ISERROR(#N/A),F3,"Shipped") ends up looking like IF(TRUE,F3,"Shipped") after the ISERROR decides #N/A is an error it recognizes.

Finally, since the IF function does the first thing after its test when the result is TRUE, we simply end up with the value of F3.

Ta'da.

Conversely, if our VLOOKUP was actually succesful in finding F3 in B, the ISERROR function would be passed the value of F3, rather than an error. In this case, ISERROR would be false, and would pass FALSE to the IF function. Then, the if function would return the value "Shipped", rather than the value of F3.

No comments:

Post a Comment