0

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.

2
  • 1
    Have you stepped through the formula with Evaluate Formula on the Formulas ribbon? What do you see?
    – Excellll
    Jan 31, 2014 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, 2014 at 8:46

3 Answers 3

2

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.

1
  • 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, 2014 at 16:49
0

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.

0

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

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