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 =IF(""=VLOOKUP(A4,$D$4:$E$18,2,FALSE),B4,IF(B4=VLOOKUP(A4,$D$4:$E$18,2,FALSE),B4,IF(B4-VLOOKUP(A4,$D$4:$E$18,2,FALSE)=0,B4,VLOOKUP(A4,$D$4:$E$18,2,FALSE))))

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

nothingwill always equalnothing, 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

feelsbetter when written that way.

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

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)))

No. 1 - You did't use the OR function as suggested.

2 - You didn't reverse the If( B4 = VLOOKUP...) termAnother 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 thisHow-To.

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

re: " What do you mean I didn't reverse the term"Re-read Response #1 from the word

Finallyon.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 thisHow-To.

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.

Ask Your Question

Weekly Poll

Do you think manufacturers should do more to reduce phone and tablet usage among kids?

Discuss in The Lounge

Poll History