Shortening a formula

November 30, 2010 at 11:12:26
Specs: Windows Vista
Hey guys so I wrote this formula and I know its right because my answers are right but is there a way to shorten this formula so it is not this long? I can't think of any other way to write it. Thanks


See More: Shortening a formula

Report •

November 30, 2010 at 11:46:48
Well, for starters, I'll offer this: Aren't these 2 terms exactly the same:

IF(B4 = VLOOKUP(A4,$D$4:$E$18,2,FALSE)

IF(B4 - VLOOKUP(A4,$D$4:$E$18,2,FALSE) = 0

In other words, if B4 minus the VLOOKUP result equals 0 then B4 must equal the VLOOKUP result. You seem to be checking for this condition twice.

Next, you should look up the OR function in Excel Help. If you have more than one condition for which you want the same result, you can use IF(OR(this, that, the other thing), B4,...

Finally, while it won't actually shorten your formula, the proper (traditional?) syntax is to put the function first and the result you are checking for second.

IF("" = VLOOKUP(A4,$D$4:$E$18,2,FALSE), should be written as:

IF(VLOOKUP(A4,$D$4:$E$18,2,FALSE) = "",

No, it doesn't really change anything, other than the fact that most (all?) people do it that way, so it makes the formula much easier for everyone to read.

Think of it this way:

The nothing will always equal nothing, but the VLOOKUP result could equal many things. Therefore we ask if the VLOOKUP result equals a specific value, not if a specific value equals the result of the VLOOKUP.

It just feels better when written that way.

Posting Tip: Before posting Data or VBA Code, read this How-To.

Report •

November 30, 2010 at 12:08:02
Thank you for the quick response. So in other words this would be best

=IF(VLOOKUP(A4,$D$4:$E$18,2,FALSE) = "",B4,IF(B4=VLOOKUP(A4,$D$4:$E$18,2,FALSE),B4,VLOOKUP(A4,$D$4:$E$18,2,FALSE)))

Report •

November 30, 2010 at 13:00:41

1 - You did't use the OR function as suggested.
2 - You didn't reverse the If( B4 = VLOOKUP...) term

Another question:

When you use IF(VLOOKUP(A4,$D$4:$E$18,2,FALSE) = "" what exactly are you checking for? Explain it in your own words.

Posting Tip: Before posting Data or VBA Code, read this How-To.

Report •

Related Solutions

November 30, 2010 at 13:05:18
What do you mean I didn't reverse the term. What im looking for is: is this is the same leave as it, if different take that form, is blank leave it the same

Report •

November 30, 2010 at 13:34:16
re: "What do you mean I didn't reverse the term"

Re-read Response #1 from the word Finally on.

You seem to have followed that advice for:

IF(VLOOKUP(A4,$D$4:$E$18,2,FALSE) = "")

but not for:

IF(B4 = VLOOKUP(A4,$D$4:$E$18,2,FALSE)

What you have written will work, it's just not the traditional way of writing an IF statement to check a function vs. a value. I'd be curious to know if your teacher feels the same way.

In addition, you can can shorten your formula by a few characters (and make it easier to understand) if you use the OR function.

Again, it will work without the OR, but it's shorter - and more elegant - with it.

If you are getting points for the shortest formula (or losing them them based on length) then using OR will make it shorter as will replacing FALSE with 0.

Posting Tip: Before posting Data or VBA Code, read this How-To.

Report •

November 30, 2010 at 13:40:20
It doesn't matter if its short or long I just felt like mine was 3 pages long LOL. But I see what your saying thank you very much.

Report •

Ask Question