<I only get a blank with the second formula>
That is why I advised you to use your FIRST formula:
=IF(ISNA(VLOOKUP(K2,B2:B8,1,FALSE)),"","X")
I also asked you to post the values of B2:B8 and K2.
--
Kind regards,
Niek Otten
Microsoft MVP - Excel
| My problem is, I want the X to appear and I only get a blank with the second
| formula, when the finds the value I want an x to appear, can you help? Thanks
| for your help and responses
|
| "Niek Otten" wrote:
|
| > You wrote in that post that you could get the X with that formula!
| >
| > Perhaps better to do this stepwise.
| > In C1:
| > =VLOOKUP(K2,B2:B8,1,FALSE)
| > In C2:
| > =ISNA(C1)
| > In C3:
| > =IF(C2,"","X")
| >
| > Now you should be able to see where it is going wrong.
| > Don't hesitate to post again; then also post the values in B2:B8 and in K2
| >
| > --
| > Kind regards,
| >
| > Niek Otten
| > Microsoft MVP - Excel
| >
| > | as stated in my first post,,it comes up blank
| > |
| > | "Niek Otten" wrote:
| > |
| > | > Use your first formula
| > | >
| > | > --
| > | > Kind regards,
| > | >
| > | > Niek Otten
| > | > Microsoft MVP - Excel
| > | >
| > | > | k2 does contain value from b2:b8 and I am wanting an "x" to appear,,,how do I
| > | > | make this happen?
| > | > |
| > | > | "David Biddulph" wrote:
| > | > |
| > | > | > Your second formula is correct for returning X if the expression is True.
| > | > | > If X isn't appearing, it is presumably because your ISNA expression is
| > | > | > returning False. Your ISNA will return true (and thus display X) if K2
| > | > | > contains something which doesn't occur in B2:B8.
| > | > | > --
| > | > | > David Biddulph
| > | > | >
| > | > | > | > | > | > > But how do I get the "X" to appear if true? That is my objective.
| > | > | > >
| > | > | > > "Niek Otten" wrote:
| > | > | > >
| > | > | > >> Apparently the value of K2 can be found in the range B2:B8. So Vlookup
| > | > | > >> does not return NA#. So ISNA returns FALSE. So you don't
| > | > | > >> get the first value from IF ("") (which you would get if ISNA had
| > | > | > >> returned TRUE), but the second one, "X".
| > | > | > >> Exactly the opposite is the case in your second formula.
| > | > | > >>
| > | > | > >> --
| > | > | > >> Kind regards,
| > | > | > >>
| > | > | > >> Niek Otten
| > | > | > >> Microsoft MVP - Excel
| > | > | > >>
| > | > | > >> | > | > | > >> |I can get the "X" to appear in this formula:
| > | > | > >> | =IF(ISNA(VLOOKUP(K2,B2:B8,1,FALSE)),"","X")
| > | > | > >> |
| > | > | > >> | But cannot get it to appear in this formula:
| > | > | > >> | =IF(ISNA(VLOOKUP(K2,B2:B8,1,FALSE)),"X","")
| > | > | > >> |
| > | > | > >> | Why?????????????
| > | > | > >>
| > | > | > >>
| > | > | > >>
| > | > | >
| > | > | >
| > | > | >
| > | >
| > | >
| > | >
| >
| >
| >