how to match value and then use if function
I want to create a third table, where I could first return the common numbers between two available tables - this part is easy.. vlookup works fine.
The next step is to compare whether the values in ER, SR, SM, EC columns are similar based on the fact that they have the same key in column "Number" enter image description here. For this purpose, I am trying to build - if(vlookup or iferror formulas - but I don't really get it.
Ideally, third table should look like:
ER SR SM EC
e.g. number 100002 (in 2nd row) False True True False
ER and EC return false because the values are different in two tables, while SR and SM return True as values are the same (both NULL).
I would appreciate your help guys!
excel if-statement excel-formula vlookup
asked Nov 14 '18 at 9:07
DmitriyDmitriy
81
add a comment |
I want to create a third table, where I could first return the common numbers between two available tables - this part is easy.. vlookup works fine.
The next step is to compare whether the values in ER, SR, SM, EC columns are similar based on the fact that they have the same key in column "Number" enter image description here. For this purpose, I am trying to build - if(vlookup or iferror formulas - but I don't really get it.
Ideally, third table should look like:
ER SR SM EC
e.g. number 100002 (in 2nd row) False True True False
ER and EC return false because the values are different in two tables, while SR and SM return True as values are the same (both NULL).
I would appreciate your help guys!
excel if-statement excel-formula vlookup
asked Nov 14 '18 at 9:07
DmitriyDmitriy
81
add a comment |
I want to create a third table, where I could first return the common numbers between two available tables - this part is easy.. vlookup works fine.
The next step is to compare whether the values in ER, SR, SM, EC columns are similar based on the fact that they have the same key in column "Number" enter image description here. For this purpose, I am trying to build - if(vlookup or iferror formulas - but I don't really get it.
Ideally, third table should look like:
ER SR SM EC
e.g. number 100002 (in 2nd row) False True True False
ER and EC return false because the values are different in two tables, while SR and SM return True as values are the same (both NULL).
I would appreciate your help guys!
excel if-statement excel-formula vlookup
asked Nov 14 '18 at 9:07
DmitriyDmitriy
81
I want to create a third table, where I could first return the common numbers between two available tables - this part is easy.. vlookup works fine.
The next step is to compare whether the values in ER, SR, SM, EC columns are similar based on the fact that they have the same key in column "Number" enter image description here. For this purpose, I am trying to build - if(vlookup or iferror formulas - but I don't really get it.
Ideally, third table should look like:
ER SR SM EC
e.g. number 100002 (in 2nd row) False True True False
ER and EC return false because the values are different in two tables, while SR and SM return True as values are the same (both NULL).
I would appreciate your help guys!
excel if-statement excel-formula vlookup
excel if-statement excel-formula vlookup
asked Nov 14 '18 at 9:07
DmitriyDmitriy
81
asked Nov 14 '18 at 9:07
DmitriyDmitriy
81
asked Nov 14 '18 at 9:07
DmitriyDmitriy
81
asked Nov 14 '18 at 9:07
DmitriyDmitriy
81
asked Nov 14 '18 at 9:07
DmitriyDmitriy
81
81
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
Assuming the "third table " starts at T2 cell.. where T1 is "number", U1 is "ER",V1 is "SR",W1 is "SM",X1 is "EC". Also assuming that you will have the "number" values filled up.
put in U2 =if(index(B:B,match($T2,$A:$A,0))=index(L:L,match($T2,$K:$K,0)),TRUE,FALSE)
then drag it to the right + downwards.
edited Nov 16 '18 at 1:15
Pang
6,8991664102
answered Nov 15 '18 at 15:23
p._phidot_p._phidot_
7471315
It actually worked, thank you for sharing this formula! However, I showed this to my manager, and he wants more granularity now... So, I came up with the following formula: IF(ISNA(VLOOKUP(U10,K:L,2,FALSE)),"Miss",(IF(VLOOKUP(U10,A:B,2,FALSE)="NULL","Add",IF(VLOOKUP(U10,A:B,2,FALSE)=VLOOKUP(U10,K:L,2,FALSE),"Ok","UNKNOWN CONDITION")))) -> However, it does not satisfy all the conditions. If values show NULL for the same number in both tables - it doesn't return "OK" instead I am getting back "Add". I wonder if you might have an idea why it's happening.
– Dmitriy
Nov 15 '18 at 16:45
What do you mean by "granularity" ? Any Example? || btw if this really IS another question.. I think it should be either : (a) update the details in the question above. or (b) start a new question. || Fyi, you may click the 'tick' mark beside this solution if at had answered your original question. ( :
– p._phidot_
Nov 15 '18 at 18:51
" However, it does not satisfy all the conditions. If values show NULL for the same number in both tables " -> would you mind sharing the full sample data? couldn't see what you mean without one.. || it doesn't return "OK" instead I am getting back "Add". -> Which cell are you referring to? (^_^)
– p._phidot_
Nov 22 '18 at 15:39
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53296462%2fhow-to-match-value-and-then-use-if-function%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Assuming the "third table " starts at T2 cell.. where T1 is "number", U1 is "ER",V1 is "SR",W1 is "SM",X1 is "EC". Also assuming that you will have the "number" values filled up.
put in U2 =if(index(B:B,match($T2,$A:$A,0))=index(L:L,match($T2,$K:$K,0)),TRUE,FALSE)
then drag it to the right + downwards.
edited Nov 16 '18 at 1:15
Pang
6,8991664102
answered Nov 15 '18 at 15:23
p._phidot_p._phidot_
7471315
It actually worked, thank you for sharing this formula! However, I showed this to my manager, and he wants more granularity now... So, I came up with the following formula: IF(ISNA(VLOOKUP(U10,K:L,2,FALSE)),"Miss",(IF(VLOOKUP(U10,A:B,2,FALSE)="NULL","Add",IF(VLOOKUP(U10,A:B,2,FALSE)=VLOOKUP(U10,K:L,2,FALSE),"Ok","UNKNOWN CONDITION")))) -> However, it does not satisfy all the conditions. If values show NULL for the same number in both tables - it doesn't return "OK" instead I am getting back "Add". I wonder if you might have an idea why it's happening.
– Dmitriy
Nov 15 '18 at 16:45
What do you mean by "granularity" ? Any Example? || btw if this really IS another question.. I think it should be either : (a) update the details in the question above. or (b) start a new question. || Fyi, you may click the 'tick' mark beside this solution if at had answered your original question. ( :
– p._phidot_
Nov 15 '18 at 18:51
" However, it does not satisfy all the conditions. If values show NULL for the same number in both tables " -> would you mind sharing the full sample data? couldn't see what you mean without one.. || it doesn't return "OK" instead I am getting back "Add". -> Which cell are you referring to? (^_^)
– p._phidot_
Nov 22 '18 at 15:39
add a comment |
Assuming the "third table " starts at T2 cell.. where T1 is "number", U1 is "ER",V1 is "SR",W1 is "SM",X1 is "EC". Also assuming that you will have the "number" values filled up.
put in U2 =if(index(B:B,match($T2,$A:$A,0))=index(L:L,match($T2,$K:$K,0)),TRUE,FALSE)
then drag it to the right + downwards.
edited Nov 16 '18 at 1:15
Pang
6,8991664102
answered Nov 15 '18 at 15:23
p._phidot_p._phidot_
7471315
It actually worked, thank you for sharing this formula! However, I showed this to my manager, and he wants more granularity now... So, I came up with the following formula: IF(ISNA(VLOOKUP(U10,K:L,2,FALSE)),"Miss",(IF(VLOOKUP(U10,A:B,2,FALSE)="NULL","Add",IF(VLOOKUP(U10,A:B,2,FALSE)=VLOOKUP(U10,K:L,2,FALSE),"Ok","UNKNOWN CONDITION")))) -> However, it does not satisfy all the conditions. If values show NULL for the same number in both tables - it doesn't return "OK" instead I am getting back "Add". I wonder if you might have an idea why it's happening.
– Dmitriy
Nov 15 '18 at 16:45
What do you mean by "granularity" ? Any Example? || btw if this really IS another question.. I think it should be either : (a) update the details in the question above. or (b) start a new question. || Fyi, you may click the 'tick' mark beside this solution if at had answered your original question. ( :
– p._phidot_
Nov 15 '18 at 18:51
" However, it does not satisfy all the conditions. If values show NULL for the same number in both tables " -> would you mind sharing the full sample data? couldn't see what you mean without one.. || it doesn't return "OK" instead I am getting back "Add". -> Which cell are you referring to? (^_^)
– p._phidot_
Nov 22 '18 at 15:39
add a comment |
Assuming the "third table " starts at T2 cell.. where T1 is "number", U1 is "ER",V1 is "SR",W1 is "SM",X1 is "EC". Also assuming that you will have the "number" values filled up.
put in U2 =if(index(B:B,match($T2,$A:$A,0))=index(L:L,match($T2,$K:$K,0)),TRUE,FALSE)
then drag it to the right + downwards.
edited Nov 16 '18 at 1:15
Pang
6,8991664102
answered Nov 15 '18 at 15:23
p._phidot_p._phidot_
7471315
Assuming the "third table " starts at T2 cell.. where T1 is "number", U1 is "ER",V1 is "SR",W1 is "SM",X1 is "EC". Also assuming that you will have the "number" values filled up.
put in U2 =if(index(B:B,match($T2,$A:$A,0))=index(L:L,match($T2,$K:$K,0)),TRUE,FALSE)
then drag it to the right + downwards.
edited Nov 16 '18 at 1:15
Pang
6,8991664102
answered Nov 15 '18 at 15:23
p._phidot_p._phidot_
7471315
edited Nov 16 '18 at 1:15
Pang
6,8991664102
edited Nov 16 '18 at 1:15
Pang
6,8991664102
edited Nov 16 '18 at 1:15
Pang
6,8991664102
6,8991664102
answered Nov 15 '18 at 15:23
p._phidot_p._phidot_
7471315
answered Nov 15 '18 at 15:23
p._phidot_p._phidot_
7471315
answered Nov 15 '18 at 15:23
p._phidot_p._phidot_
7471315
7471315
It actually worked, thank you for sharing this formula! However, I showed this to my manager, and he wants more granularity now... So, I came up with the following formula: IF(ISNA(VLOOKUP(U10,K:L,2,FALSE)),"Miss",(IF(VLOOKUP(U10,A:B,2,FALSE)="NULL","Add",IF(VLOOKUP(U10,A:B,2,FALSE)=VLOOKUP(U10,K:L,2,FALSE),"Ok","UNKNOWN CONDITION")))) -> However, it does not satisfy all the conditions. If values show NULL for the same number in both tables - it doesn't return "OK" instead I am getting back "Add". I wonder if you might have an idea why it's happening.
– Dmitriy
Nov 15 '18 at 16:45
What do you mean by "granularity" ? Any Example? || btw if this really IS another question.. I think it should be either : (a) update the details in the question above. or (b) start a new question. || Fyi, you may click the 'tick' mark beside this solution if at had answered your original question. ( :
– p._phidot_
Nov 15 '18 at 18:51
" However, it does not satisfy all the conditions. If values show NULL for the same number in both tables " -> would you mind sharing the full sample data? couldn't see what you mean without one.. || it doesn't return "OK" instead I am getting back "Add". -> Which cell are you referring to? (^_^)
– p._phidot_
Nov 22 '18 at 15:39
add a comment |
It actually worked, thank you for sharing this formula! However, I showed this to my manager, and he wants more granularity now... So, I came up with the following formula: IF(ISNA(VLOOKUP(U10,K:L,2,FALSE)),"Miss",(IF(VLOOKUP(U10,A:B,2,FALSE)="NULL","Add",IF(VLOOKUP(U10,A:B,2,FALSE)=VLOOKUP(U10,K:L,2,FALSE),"Ok","UNKNOWN CONDITION")))) -> However, it does not satisfy all the conditions. If values show NULL for the same number in both tables - it doesn't return "OK" instead I am getting back "Add". I wonder if you might have an idea why it's happening.
– Dmitriy
Nov 15 '18 at 16:45
What do you mean by "granularity" ? Any Example? || btw if this really IS another question.. I think it should be either : (a) update the details in the question above. or (b) start a new question. || Fyi, you may click the 'tick' mark beside this solution if at had answered your original question. ( :
– p._phidot_
Nov 15 '18 at 18:51
" However, it does not satisfy all the conditions. If values show NULL for the same number in both tables " -> would you mind sharing the full sample data? couldn't see what you mean without one.. || it doesn't return "OK" instead I am getting back "Add". -> Which cell are you referring to? (^_^)
– p._phidot_
Nov 22 '18 at 15:39
It actually worked, thank you for sharing this formula! However, I showed this to my manager, and he wants more granularity now... So, I came up with the following formula: IF(ISNA(VLOOKUP(U10,K:L,2,FALSE)),"Miss",(IF(VLOOKUP(U10,A:B,2,FALSE)="NULL","Add",IF(VLOOKUP(U10,A:B,2,FALSE)=VLOOKUP(U10,K:L,2,FALSE),"Ok","UNKNOWN CONDITION")))) -> However, it does not satisfy all the conditions. If values show NULL for the same number in both tables - it doesn't return "OK" instead I am getting back "Add". I wonder if you might have an idea why it's happening.
– Dmitriy
Nov 15 '18 at 16:45
It actually worked, thank you for sharing this formula! However, I showed this to my manager, and he wants more granularity now... So, I came up with the following formula: IF(ISNA(VLOOKUP(U10,K:L,2,FALSE)),"Miss",(IF(VLOOKUP(U10,A:B,2,FALSE)="NULL","Add",IF(VLOOKUP(U10,A:B,2,FALSE)=VLOOKUP(U10,K:L,2,FALSE),"Ok","UNKNOWN CONDITION")))) -> However, it does not satisfy all the conditions. If values show NULL for the same number in both tables - it doesn't return "OK" instead I am getting back "Add". I wonder if you might have an idea why it's happening.
– Dmitriy
Nov 15 '18 at 16:45
What do you mean by "granularity" ? Any Example? || btw if this really IS another question.. I think it should be either : (a) update the details in the question above. or (b) start a new question. || Fyi, you may click the 'tick' mark beside this solution if at had answered your original question. ( :
– p._phidot_
Nov 15 '18 at 18:51
What do you mean by "granularity" ? Any Example? || btw if this really IS another question.. I think it should be either : (a) update the details in the question above. or (b) start a new question. || Fyi, you may click the 'tick' mark beside this solution if at had answered your original question. ( :
– p._phidot_
Nov 15 '18 at 18:51
" However, it does not satisfy all the conditions. If values show NULL for the same number in both tables " -> would you mind sharing the full sample data? couldn't see what you mean without one.. || it doesn't return "OK" instead I am getting back "Add". -> Which cell are you referring to? (^_^)
– p._phidot_
Nov 22 '18 at 15:39
" However, it does not satisfy all the conditions. If values show NULL for the same number in both tables " -> would you mind sharing the full sample data? couldn't see what you mean without one.. || it doesn't return "OK" instead I am getting back "Add". -> Which cell are you referring to? (^_^)
– p._phidot_
Nov 22 '18 at 15:39
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53296462%2fhow-to-match-value-and-then-use-if-function%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
