using cell value in a formula

[smakatura]

• A2 contains a forumula that returns a value of 12345.
  • of course when you select on A2 it shows the forumula that creates that value.
• in b2 I want to add a formula that
  • looks at the final value of A2 (12345)
  • looks for it on a list of values in column c
  • if result is N/A (bc value is not on C) - says NO
  • if result is not N/A (bc value is on C) - says Yes

my initial thoughts are:

=if(isna(lookup(value(b2),c:c))=true, "NO", "YES"

so if the value of the cell is in column C, it puts yes, if not in the column puts No.

for some reason it is not working. I think it has to do with using the value function inside the isna/lookup function.

any suggestions

 

[Aladin Akyurek]

• A2 contains a forumula that returns a value of 12345.
  • of course when you select on A2 it shows the forumula that creates that value.
• in b2 I want to add a formula that
  • looks at the final value of A2 (12345)
  • looks for it on a list of values in column c
  • if result is N/A (bc value is not on C) - says NO
  • if result is not N/A (bc value is on C) - says Yes

my initial thoughts are:

=if(isna(lookup(value(b2),c:c))=true, "NO", "YES"

so if the value of the cell is in column C, it puts yes, if not in the column puts No.

for some reason it is not working. I think it has to do with using the value function inside the isna/lookup function.

any suggestions

Try...

=IF(ISNUMBER(MATCH(A2,C:C,0)),"Yes","No")

With VLOOKUP, it would be:

=IF(ISNA(VLOOKUP(A2,C:C,1,0)),"No","Yes")

 

[smakatura]

Turns out my original formula did work (I figured it out after I posted the original post). but I love that "isnumber" function, i had not used that before

that worked great

thanks

 

[Aladin Akyurek]

Turns out my original formula did work (I figured it out after I posted the original post).

LOOKUP would require that C:C is in ascending order. If CC is indeed in ascending order, assuming exact match, try rather:

=IF(ISNUMBER(1/(LOOKUP(B2,C:C)=B2),"Yes","No")

but I love that "isnumber" function, i had not used that before

that worked great

thanks

You are welcome. Thanks for providing feedback.