Advanced Lookup Formula
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I am in need of an advanced lookup formula that will search through data from one table and produce a value based on conditions from another table.
What I am trying to achieve is this:
- If Column J in Table 1 is equal to column A in Table 2 and
- Column L in Table 1 is equal to Column D in Table 2
- But Column D in Table 2 is blank but there are values in Column L of Table 2 except the value specified in Table 2 previously
- And Column G in Table 1 is equal to Column B in Table 2
- But Column B in table 2 is blank but Column G in Table 1 has values except the one specified previously in Table 2
- And if Column O in Table 1 is equal to Column C in Table 2
- But Column C in Table 2 is Blank and Column O in Table 2 contains values other than previously specified in Table 2
- The end result should be column E in Table 2.
I used this formula but it is showing a value error
=IF(AND(Trans!J:J=Mapping!A:A),Mapping!E:E),IF(AND(Trans!L:L=Mapping!D:D),Mapping!E:E),IF(AND(Mapping!D:D="",Trans!L:L<>""),Mapping!E:E), IF(AND(Trans!G:G=Mapping!B:B),Mapping!E:E),IF(AND(Mapping!B:B="",Trans!G:G<>""),Mapping!E:E),IF(AND(Trans!O:O=Mapping!C:C),Mapping!E:E),IF(AND(Mapping!C:C="",Trans!O:O<>""),Mapping!E:E)
The formula can also be searching by row. I would just like something that works!
Please help.
excel if-statement vlookup
edited Nov 24 '18 at 23:35
Forward Ed
7,11311339
asked Nov 24 '18 at 22:54
EBKEBK
11
add a comment |
I am in need of an advanced lookup formula that will search through data from one table and produce a value based on conditions from another table.
What I am trying to achieve is this:
- If Column J in Table 1 is equal to column A in Table 2 and
- Column L in Table 1 is equal to Column D in Table 2
- But Column D in Table 2 is blank but there are values in Column L of Table 2 except the value specified in Table 2 previously
- And Column G in Table 1 is equal to Column B in Table 2
- But Column B in table 2 is blank but Column G in Table 1 has values except the one specified previously in Table 2
- And if Column O in Table 1 is equal to Column C in Table 2
- But Column C in Table 2 is Blank and Column O in Table 2 contains values other than previously specified in Table 2
- The end result should be column E in Table 2.
I used this formula but it is showing a value error
=IF(AND(Trans!J:J=Mapping!A:A),Mapping!E:E),IF(AND(Trans!L:L=Mapping!D:D),Mapping!E:E),IF(AND(Mapping!D:D="",Trans!L:L<>""),Mapping!E:E), IF(AND(Trans!G:G=Mapping!B:B),Mapping!E:E),IF(AND(Mapping!B:B="",Trans!G:G<>""),Mapping!E:E),IF(AND(Trans!O:O=Mapping!C:C),Mapping!E:E),IF(AND(Mapping!C:C="",Trans!O:O<>""),Mapping!E:E)
The formula can also be searching by row. I would just like something that works!
Please help.
excel if-statement vlookup
edited Nov 24 '18 at 23:35
Forward Ed
7,11311339
asked Nov 24 '18 at 22:54
EBKEBK
11
looks like you may have a bracket out of place with your formula. Right now the close bracket aftermapping!E:Eis actually pairing up with your IF function. This means you have no FALSE option for your IF formula. Everything after this point is not doing a thing but causing problems at the moment.
– Forward Ed
Nov 24 '18 at 23:47
break your formula down into smaller pieces. make sure each piece is working. when combining combine one at a time to make sure each part is combined correctly
– Forward Ed
Nov 25 '18 at 0:38
I have broken it down and the issue seems to be that even though the text is identical, it is not recognising it as such. I used the exact formula which suggests they are a match but the match formula says otherwise. Does anyone know how I can make excel recognise that the text is identical? I have tried using trim.
– EBK
Nov 26 '18 at 16:29
In excel the IF funtcion works like this:IF(CONDITION TRUE/FALSE, What to do if TRUE, What to do if FALSE). Normally this is along the lines of something likeIF(A1=B1,"They are equal", "They are not equal"). That would be placed in C1 and then copied down. As you copy the 1 will increase to 2 then 3 , etc.
– Forward Ed
Nov 26 '18 at 18:26
add a comment |
I am in need of an advanced lookup formula that will search through data from one table and produce a value based on conditions from another table.
What I am trying to achieve is this:
- If Column J in Table 1 is equal to column A in Table 2 and
- Column L in Table 1 is equal to Column D in Table 2
- But Column D in Table 2 is blank but there are values in Column L of Table 2 except the value specified in Table 2 previously
- And Column G in Table 1 is equal to Column B in Table 2
- But Column B in table 2 is blank but Column G in Table 1 has values except the one specified previously in Table 2
- And if Column O in Table 1 is equal to Column C in Table 2
- But Column C in Table 2 is Blank and Column O in Table 2 contains values other than previously specified in Table 2
- The end result should be column E in Table 2.
I used this formula but it is showing a value error
=IF(AND(Trans!J:J=Mapping!A:A),Mapping!E:E),IF(AND(Trans!L:L=Mapping!D:D),Mapping!E:E),IF(AND(Mapping!D:D="",Trans!L:L<>""),Mapping!E:E), IF(AND(Trans!G:G=Mapping!B:B),Mapping!E:E),IF(AND(Mapping!B:B="",Trans!G:G<>""),Mapping!E:E),IF(AND(Trans!O:O=Mapping!C:C),Mapping!E:E),IF(AND(Mapping!C:C="",Trans!O:O<>""),Mapping!E:E)
The formula can also be searching by row. I would just like something that works!
Please help.
excel if-statement vlookup
edited Nov 24 '18 at 23:35
Forward Ed
7,11311339
asked Nov 24 '18 at 22:54
EBKEBK
11
I am in need of an advanced lookup formula that will search through data from one table and produce a value based on conditions from another table.
What I am trying to achieve is this:
- If Column J in Table 1 is equal to column A in Table 2 and
- Column L in Table 1 is equal to Column D in Table 2
- But Column D in Table 2 is blank but there are values in Column L of Table 2 except the value specified in Table 2 previously
- And Column G in Table 1 is equal to Column B in Table 2
- But Column B in table 2 is blank but Column G in Table 1 has values except the one specified previously in Table 2
- And if Column O in Table 1 is equal to Column C in Table 2
- But Column C in Table 2 is Blank and Column O in Table 2 contains values other than previously specified in Table 2
- The end result should be column E in Table 2.
I used this formula but it is showing a value error
=IF(AND(Trans!J:J=Mapping!A:A),Mapping!E:E),IF(AND(Trans!L:L=Mapping!D:D),Mapping!E:E),IF(AND(Mapping!D:D="",Trans!L:L<>""),Mapping!E:E), IF(AND(Trans!G:G=Mapping!B:B),Mapping!E:E),IF(AND(Mapping!B:B="",Trans!G:G<>""),Mapping!E:E),IF(AND(Trans!O:O=Mapping!C:C),Mapping!E:E),IF(AND(Mapping!C:C="",Trans!O:O<>""),Mapping!E:E)
The formula can also be searching by row. I would just like something that works!
Please help.
excel if-statement vlookup
excel if-statement vlookup
edited Nov 24 '18 at 23:35
Forward Ed
7,11311339
asked Nov 24 '18 at 22:54
EBKEBK
11
edited Nov 24 '18 at 23:35
Forward Ed
7,11311339
asked Nov 24 '18 at 22:54
EBKEBK
11
edited Nov 24 '18 at 23:35
Forward Ed
7,11311339
edited Nov 24 '18 at 23:35
Forward Ed
7,11311339
edited Nov 24 '18 at 23:35
Forward Ed
7,11311339
7,11311339
asked Nov 24 '18 at 22:54
EBKEBK
11
asked Nov 24 '18 at 22:54
EBKEBK
11
asked Nov 24 '18 at 22:54
EBKEBK
11
11
looks like you may have a bracket out of place with your formula. Right now the close bracket aftermapping!E:Eis actually pairing up with your IF function. This means you have no FALSE option for your IF formula. Everything after this point is not doing a thing but causing problems at the moment.
– Forward Ed
Nov 24 '18 at 23:47
break your formula down into smaller pieces. make sure each piece is working. when combining combine one at a time to make sure each part is combined correctly
– Forward Ed
Nov 25 '18 at 0:38
I have broken it down and the issue seems to be that even though the text is identical, it is not recognising it as such. I used the exact formula which suggests they are a match but the match formula says otherwise. Does anyone know how I can make excel recognise that the text is identical? I have tried using trim.
– EBK
Nov 26 '18 at 16:29
In excel the IF funtcion works like this:IF(CONDITION TRUE/FALSE, What to do if TRUE, What to do if FALSE). Normally this is along the lines of something likeIF(A1=B1,"They are equal", "They are not equal"). That would be placed in C1 and then copied down. As you copy the 1 will increase to 2 then 3 , etc.
– Forward Ed
Nov 26 '18 at 18:26
add a comment |
looks like you may have a bracket out of place with your formula. Right now the close bracket aftermapping!E:Eis actually pairing up with your IF function. This means you have no FALSE option for your IF formula. Everything after this point is not doing a thing but causing problems at the moment.
– Forward Ed
Nov 24 '18 at 23:47
break your formula down into smaller pieces. make sure each piece is working. when combining combine one at a time to make sure each part is combined correctly
– Forward Ed
Nov 25 '18 at 0:38
I have broken it down and the issue seems to be that even though the text is identical, it is not recognising it as such. I used the exact formula which suggests they are a match but the match formula says otherwise. Does anyone know how I can make excel recognise that the text is identical? I have tried using trim.
– EBK
Nov 26 '18 at 16:29
In excel the IF funtcion works like this:IF(CONDITION TRUE/FALSE, What to do if TRUE, What to do if FALSE). Normally this is along the lines of something likeIF(A1=B1,"They are equal", "They are not equal"). That would be placed in C1 and then copied down. As you copy the 1 will increase to 2 then 3 , etc.
– Forward Ed
Nov 26 '18 at 18:26
looks like you may have a bracket out of place with your formula. Right now the close bracket after
mapping!E:E is actually pairing up with your IF function. This means you have no FALSE option for your IF formula. Everything after this point is not doing a thing but causing problems at the moment.– Forward Ed
Nov 24 '18 at 23:47
looks like you may have a bracket out of place with your formula. Right now the close bracket after
mapping!E:E is actually pairing up with your IF function. This means you have no FALSE option for your IF formula. Everything after this point is not doing a thing but causing problems at the moment.– Forward Ed
Nov 24 '18 at 23:47
break your formula down into smaller pieces. make sure each piece is working. when combining combine one at a time to make sure each part is combined correctly
– Forward Ed
Nov 25 '18 at 0:38
break your formula down into smaller pieces. make sure each piece is working. when combining combine one at a time to make sure each part is combined correctly
– Forward Ed
Nov 25 '18 at 0:38
I have broken it down and the issue seems to be that even though the text is identical, it is not recognising it as such. I used the exact formula which suggests they are a match but the match formula says otherwise. Does anyone know how I can make excel recognise that the text is identical? I have tried using trim.
– EBK
Nov 26 '18 at 16:29
I have broken it down and the issue seems to be that even though the text is identical, it is not recognising it as such. I used the exact formula which suggests they are a match but the match formula says otherwise. Does anyone know how I can make excel recognise that the text is identical? I have tried using trim.
– EBK
Nov 26 '18 at 16:29
In excel the IF funtcion works like this:
IF(CONDITION TRUE/FALSE, What to do if TRUE, What to do if FALSE). Normally this is along the lines of something like IF(A1=B1,"They are equal", "They are not equal"). That would be placed in C1 and then copied down. As you copy the 1 will increase to 2 then 3 , etc.– Forward Ed
Nov 26 '18 at 18:26
In excel the IF funtcion works like this:
IF(CONDITION TRUE/FALSE, What to do if TRUE, What to do if FALSE). Normally this is along the lines of something like IF(A1=B1,"They are equal", "They are not equal"). That would be placed in C1 and then copied down. As you copy the 1 will increase to 2 then 3 , etc.– Forward Ed
Nov 26 '18 at 18:26
add a comment |
1 Answer
1
active
oldest
votes
Your formula is doing nothing, it seems you are combining all the logics you thought in one place, but it is not in a form that excel can understand.
As per my understanding you have to remove the blank checks, as per your data they may return unexpected results, if their is one blank or if there are more in both cases result would be different, you would not know the reason of a returned value. (it is not logical to make a check like that)
A logic like this may return more than one value, you have to put some formula in case there are more values returned.
AS for the formula, you are comparing a full column with a full column. It is not recommended to do that, the recommended way is to compare one value to a range of values and the function AND() is not required in the way you are using it
a comparison in the formula would be something like
=IF(Trans!J3=Mapping!A3:A20,Mapping!E3:E20,"")
and it is an array formula that can return multiple values
At the end summary is that first you have to define the logic you are making, it is not clear and not accounting for all possibilities after that learn how to apply such formulas and then apply it on your data.
answered Nov 25 '18 at 6:33
usmanhaqusmanhaq
1,113129
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%2f53463075%2fadvanced-lookup-formula%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
Your formula is doing nothing, it seems you are combining all the logics you thought in one place, but it is not in a form that excel can understand.
As per my understanding you have to remove the blank checks, as per your data they may return unexpected results, if their is one blank or if there are more in both cases result would be different, you would not know the reason of a returned value. (it is not logical to make a check like that)
A logic like this may return more than one value, you have to put some formula in case there are more values returned.
AS for the formula, you are comparing a full column with a full column. It is not recommended to do that, the recommended way is to compare one value to a range of values and the function AND() is not required in the way you are using it
a comparison in the formula would be something like
=IF(Trans!J3=Mapping!A3:A20,Mapping!E3:E20,"")
and it is an array formula that can return multiple values
At the end summary is that first you have to define the logic you are making, it is not clear and not accounting for all possibilities after that learn how to apply such formulas and then apply it on your data.
answered Nov 25 '18 at 6:33
usmanhaqusmanhaq
1,113129
add a comment |
Your formula is doing nothing, it seems you are combining all the logics you thought in one place, but it is not in a form that excel can understand.
As per my understanding you have to remove the blank checks, as per your data they may return unexpected results, if their is one blank or if there are more in both cases result would be different, you would not know the reason of a returned value. (it is not logical to make a check like that)
A logic like this may return more than one value, you have to put some formula in case there are more values returned.
AS for the formula, you are comparing a full column with a full column. It is not recommended to do that, the recommended way is to compare one value to a range of values and the function AND() is not required in the way you are using it
a comparison in the formula would be something like
=IF(Trans!J3=Mapping!A3:A20,Mapping!E3:E20,"")
and it is an array formula that can return multiple values
At the end summary is that first you have to define the logic you are making, it is not clear and not accounting for all possibilities after that learn how to apply such formulas and then apply it on your data.
answered Nov 25 '18 at 6:33
usmanhaqusmanhaq
1,113129
add a comment |
Your formula is doing nothing, it seems you are combining all the logics you thought in one place, but it is not in a form that excel can understand.
As per my understanding you have to remove the blank checks, as per your data they may return unexpected results, if their is one blank or if there are more in both cases result would be different, you would not know the reason of a returned value. (it is not logical to make a check like that)
A logic like this may return more than one value, you have to put some formula in case there are more values returned.
AS for the formula, you are comparing a full column with a full column. It is not recommended to do that, the recommended way is to compare one value to a range of values and the function AND() is not required in the way you are using it
a comparison in the formula would be something like
=IF(Trans!J3=Mapping!A3:A20,Mapping!E3:E20,"")
and it is an array formula that can return multiple values
At the end summary is that first you have to define the logic you are making, it is not clear and not accounting for all possibilities after that learn how to apply such formulas and then apply it on your data.
answered Nov 25 '18 at 6:33
usmanhaqusmanhaq
1,113129
Your formula is doing nothing, it seems you are combining all the logics you thought in one place, but it is not in a form that excel can understand.
As per my understanding you have to remove the blank checks, as per your data they may return unexpected results, if their is one blank or if there are more in both cases result would be different, you would not know the reason of a returned value. (it is not logical to make a check like that)
A logic like this may return more than one value, you have to put some formula in case there are more values returned.
AS for the formula, you are comparing a full column with a full column. It is not recommended to do that, the recommended way is to compare one value to a range of values and the function AND() is not required in the way you are using it
a comparison in the formula would be something like
=IF(Trans!J3=Mapping!A3:A20,Mapping!E3:E20,"")
and it is an array formula that can return multiple values
At the end summary is that first you have to define the logic you are making, it is not clear and not accounting for all possibilities after that learn how to apply such formulas and then apply it on your data.
answered Nov 25 '18 at 6:33
usmanhaqusmanhaq
1,113129
answered Nov 25 '18 at 6:33
usmanhaqusmanhaq
1,113129
answered Nov 25 '18 at 6:33
usmanhaqusmanhaq
1,113129
answered Nov 25 '18 at 6:33
usmanhaqusmanhaq
1,113129
1,113129
add a comment |
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%2f53463075%2fadvanced-lookup-formula%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
looks like you may have a bracket out of place with your formula. Right now the close bracket after
mapping!E:Eis actually pairing up with your IF function. This means you have no FALSE option for your IF formula. Everything after this point is not doing a thing but causing problems at the moment.– Forward Ed
Nov 24 '18 at 23:47
break your formula down into smaller pieces. make sure each piece is working. when combining combine one at a time to make sure each part is combined correctly
– Forward Ed
Nov 25 '18 at 0:38
I have broken it down and the issue seems to be that even though the text is identical, it is not recognising it as such. I used the exact formula which suggests they are a match but the match formula says otherwise. Does anyone know how I can make excel recognise that the text is identical? I have tried using trim.
– EBK
Nov 26 '18 at 16:29
In excel the IF funtcion works like this:
IF(CONDITION TRUE/FALSE, What to do if TRUE, What to do if FALSE). Normally this is along the lines of something likeIF(A1=B1,"They are equal", "They are not equal"). That would be placed in C1 and then copied down. As you copy the 1 will increase to 2 then 3 , etc.– Forward Ed
Nov 26 '18 at 18:26