Non-estna
Solved
Fafa m
-
fafa m -
fafa m -
Hello,
In a formula, one of my conditions is =IF(NOT(ISNA(VLOOKUP(A1;B1:C10;1;0)));0;1). Please, is there a counterpart to ISNA?
Thank you
Configuration: Windows XP / Internet Explorer 6.0
In a formula, one of my conditions is =IF(NOT(ISNA(VLOOKUP(A1;B1:C10;1;0)));0;1). Please, is there a counterpart to ISNA?
Thank you
Configuration: Windows XP / Internet Explorer 6.0
6 answers
Hello Fafa m, the forum,
To literally translate your request you can write:
=IF("not NA"(VLOOKUP(A1,B1:C10,1,0));0;1)
thus,
--
Always zen
To literally translate your request you can write:
=IF("not NA"(VLOOKUP(A1,B1:C10,1,0));0;1)
thus,
=IF(NOT(ISNA(VLOOKUP(A1,B1:C10,1,0)));0;1)
--
Always zen
Hello
I think it's better to work on the opposite:
=IF(ISERROR(VLOOKUP(A1,B1:C10,2,0)),0,1)
but curiously, this code does not return a value from C, but only 1 when the value in A1 exists in column B of the range!
hence, maybe to simplify:
=IF(COUNTIF(B1:C100,A1)=0,0,1)
For my friend Lentz
without wanting to quibble, I believe that the reverse formula you propose is exactly the same as that of the requester?
best regards
--
Let’s ask ourselves if we are not the only ones to understand what is being explained?
I think it's better to work on the opposite:
=IF(ISERROR(VLOOKUP(A1,B1:C10,2,0)),0,1)
but curiously, this code does not return a value from C, but only 1 when the value in A1 exists in column B of the range!
hence, maybe to simplify:
=IF(COUNTIF(B1:C100,A1)=0,0,1)
For my friend Lentz
without wanting to quibble, I believe that the reverse formula you propose is exactly the same as that of the requester?
best regards
--
Let’s ask ourselves if we are not the only ones to understand what is being explained?
No Lentz, I don't believe
in yours as in that of the applicant, ""is not NA" in quotes assumes that the text is written in the cell.
I thought (maybe wrongly, it's true, but given the title of the thread) that the applicant wanted to eliminate the #NA display that occurs when a formula doesn't find its criteria.
The ISERROR or ISNA functions allow for this kind of manipulation.
Best regards
in yours as in that of the applicant, ""is not NA" in quotes assumes that the text is written in the cell.
I thought (maybe wrongly, it's true, but given the title of the thread) that the applicant wanted to eliminate the #NA display that occurs when a formula doesn't find its criteria.
The ISERROR or ISNA functions allow for this kind of manipulation.
Best regards
Hello,
I apologize for the intrusion, but
regardless of the formula, if this formula returns #N/A, it indicates that there is an error; either the value in A is not valid or it does not exist in the matrix B1:C10.
VLOOKUP(A1, B1:C10, 1, 0)
no matter how you manage this error, for example with IF(ISERROR(
so, if VLOOKUP(A1, B1:C10, 1, 0) returns an error, we display 0; otherwise, we display 1, which is consistent with the formula
=IF(ISERROR(VLOOKUP(A1, B1:C10, 1, 0)), 0, 1)
unless we are trying to display the result of the search if it exists, or 0, or even nothing, or I didn't understand the request
=IF(ISERROR(VLOOKUP(A1, B1:C10, 1, 0)), 0, VLOOKUP(A1, B1:C10, 2, 0))
or
=IF(ISERROR(VLOOKUP(A1, B1:C10, 1, 0)), "", VLOOKUP(A1, B1:C10, 2, 0))
--
Best,
Mike-31
A period of failure is a perfect time to sow the seeds of knowledge.
I apologize for the intrusion, but
regardless of the formula, if this formula returns #N/A, it indicates that there is an error; either the value in A is not valid or it does not exist in the matrix B1:C10.
VLOOKUP(A1, B1:C10, 1, 0)
no matter how you manage this error, for example with IF(ISERROR(
so, if VLOOKUP(A1, B1:C10, 1, 0) returns an error, we display 0; otherwise, we display 1, which is consistent with the formula
=IF(ISERROR(VLOOKUP(A1, B1:C10, 1, 0)), 0, 1)
unless we are trying to display the result of the search if it exists, or 0, or even nothing, or I didn't understand the request
=IF(ISERROR(VLOOKUP(A1, B1:C10, 1, 0)), 0, VLOOKUP(A1, B1:C10, 2, 0))
or
=IF(ISERROR(VLOOKUP(A1, B1:C10, 1, 0)), "", VLOOKUP(A1, B1:C10, 2, 0))
--
Best,
Mike-31
A period of failure is a perfect time to sow the seeds of knowledge.
Thank you for all your responses. I poorly formulated my request; I will try to be more explicit:
In column A, I have a list of 100 names
In column B, I have a list of 10 names that can also be found in column A
In column C, numbers related to each name in column B but not important for the formula
My formula is in column D and already contains 6 conditions that should only apply if the name does not appear in column B. I am just looking to add a 7th condition that will prevent the application of the other 6 conditions as soon as the name in A appears in column B
I provided this formula as an example because I simply thought that there was an inverse formula to ISNA
I hope I have been clearer. My apologies.
In column A, I have a list of 100 names
In column B, I have a list of 10 names that can also be found in column A
In column C, numbers related to each name in column B but not important for the formula
My formula is in column D and already contains 6 conditions that should only apply if the name does not appear in column B. I am just looking to add a 7th condition that will prevent the application of the other 6 conditions as soon as the name in A appears in column B
I provided this formula as an example because I simply thought that there was an inverse formula to ISNA
I hope I have been clearer. My apologies.
Can you copy your conditional formula from column D so we can see how to nest it?
It would be best to have a part of your file without any confidential notes
to attach using one of these links
https://www.cjoint.com/ or http://www.cijoint.fr/
It would be best to have a part of your file without any confidential notes
to attach using one of these links
https://www.cjoint.com/ or http://www.cijoint.fr/
