Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

According to this and my own testing, using VLOOKUP can be compared to a specific value inside of IF.

E. g.: = IF(VLOOKUP(...) = 20, [then value], [else value]) works.

But if I compare the result of the VLOOKUP to the value in a certain cell, it does not return a value:

IF(VLOOKUP(...) = E$1, [then value], [else value])

It looks like IF does not evaluate the comparison, but only VLOOKUP. Adding brackets does not help:

IF((VLOOKUP(...) = E$1), [then value], [else value]) doesn't work either.

In the first example, I compare VLOOKUP to 20. In the second example, I want to compare it to the value in E1. (E1 has a static value, but I want to use the formula in a matrix where each field compares the result of VLOOKUP to the column heading and returns a different value if they match or not.)

How could I get this to work?

Edit: I confused the values that I compare the result of VLOOKUP to. The formula does work; you just have to use the right cells.

share|improve this question

closed as unclear what you're asking by Excellll, Raystafarian, Tog, Journeyman Geek, Dave Feb 28 at 13:01

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question.If this question can be reworded to fit the rules in the help center, please edit the question.

1  
Have you stepped through the formula with Evaluate Formula on the Formulas ribbon? What do you see? –  Excellll Jan 31 at 14:55
    
I stepped through the formula and found that it actually does work. I confused the values that I compare the result of VLOOKUP to. So, thanks for this suggestion, Excellll! –  georg w. Feb 3 at 8:46

3 Answers 3

If you are not setting in vlookup a Range_lookup value ie false, then the first column in the Table array will need to be sorted either ascending alphbetically or numerically.

share|improve this answer
    
Yes, this was my first thought too. Always run vlookup with 0 as the last parameter unless you have a very specific reason not to. –  zelanix Jan 31 at 16:49

VLOOKUP returns a value,

IF (VLOOKUP returned value = "Something" , [TRUE? Then This Value] , [FALSE? Then This Value] )

So you need to compare the Vlookup returned value to another value. If your Vlookup is simply returning the word True of False then you don't need to compare it to anything as Vlookup provides the required logical test.

share|improve this answer

If you aren't using numbers only, you need to surround strings in quotation marks

=IF(VLOOKUP("a",F1:G2,2,FALSE)=6,"yay","nay") or =IF(VLOOKUP(B1,F1:G2,2,FALSE)=A1,"yay","nay")

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.