IF/AND statement with index-matchingComparing Two Columns in Excel, Returning a ThirdIn Excel I need to find...
Did 5.25" floppies undergo a change in magnetic coating?
Contradiction with Banach Fixed Point Theorem
Must a tritone substitution use a dominant seventh chord?
If a druid in Wild Shape swallows a creature whole, then turns back to her normal form, what happens?
Why proton concentration is divided by 10⁻⁷?
What if I store 10TB on azure servers and then keep the vm powered off?
Six real numbers so that product of any five is the sixth one
CBP Reminds Travelers to Allow 72 Hours for ESTA. Why?
Book where the good guy lives backwards through time and the bad guy lives forward
Why does the author believe that the central mass that gas cloud HCN-0.009-0.044 orbits is smaller than our solar system?
How can atoms be electrically neutral when there is a difference in the positions of the charges?
As a new poet, where can I find help from a professional to judge my work?
Sometimes a banana is just a banana
Non-Italian European mafias in USA?
What to do when being responsible for data protection in your lab, yet advice is ignored?
Second-rate spelling
What is the wife of a henpecked husband called?
Closure of presentable objects under finite limits
Exponential growth/decay formula: what happened to the other constant of integration?
How can I be pwned if I'm not registered on that site?
Equivalent to "source" in OpenBSD?
Why is working on the same position for more than 15 years not a red flag?
Reason Why Dimensional Travelling Would be Restricted
"Murder!" The knight said
IF/AND statement with index-matching
Comparing Two Columns in Excel, Returning a ThirdIn Excel I need to find data matches between two arrays - one horizontal and one verticaltry to fix my Excel formulaMatching and returning values to populate columnIndex Match on multiple criteria not workinghow to use INDEX and MATCH in this condition in excel?Excel 2010 INDEX MATCH formula failure--need help to resolveIndex Match across multiple columnsIf conditions are met for TRUE response copy row to new sheetTrouble using IFERROR with an IF statement that has Multiple Conditions
So I had a very long nested-if statement, and the last nested-IF was an IF/AND statement with index matching. The formula worked fine (or so I thought), until a few weeks later when I needed to add another IF/AND statement with index-matching. Then the whole thing wouldn't even run because Excel thought I had too many arguments for the function.
So, this weekend I was putzing around with the formula, and I started parsing out bits and realized this IF/AND statement was only partially working.
Here's what I mean:
(my formula is in the Priority column (col C) of sheet 1)
If the STATUS(col. B) says Approved, I want my formula to index-match the priority from sheet 2, unless that cell is blank - then I want the priority on sheet 1 to say "Medium." If Status on sheet 1 doesn't say Approved, then I don't want my formula to do anything in that cell.
=IF((AND(B2="Approved",INDEX(Sheet2!B:B,MATCH(Sheet1!A2,Sheet2!A:A,0))="")),"Medium", INDEX(Sheet2!B:B,MATCH(Sheet1!A2,Sheet2!A:A,0)))
which goes in in col. C of sheet1:
Here is sheet 2:
Item 10 works well - Status is Approved and priority on sheet 2 is blank so col. C says "Medium."
But, col C for items 20 and 25 have been index-matched when they shouldn't - they're status is not Approved, so they should be left blank.
I had a couple of ideas about remedying this, but neither worked. One idea was to add an AND(B2="Approved") to the last index-match,
=IF((AND(B2="Approved",INDEX(Sheet2!B:B,MATCH(Sheet1!A2,Sheet2!A:A,0))="")),"Medium", (AND(B2="Approved",INDEX(Sheet2!B:B,MATCH(Sheet1!A2,Sheet2!A:A,0)))))
Which just returns a bunch of TRUE or FALSE.
I also tinkered with adding another IF into that last index-match bit, but I realized that it wouldn't be one cohesive IF statement anymore.
How can I change my formula to get it to work? Sorry if the answer is obvious, I'm pretty new to Excel.
microsoft-excel worksheet-function microsoft-excel-2010 microsoft-excel-2007
asked 8 hours ago
forlornforlorn
103
add a comment |
So I had a very long nested-if statement, and the last nested-IF was an IF/AND statement with index matching. The formula worked fine (or so I thought), until a few weeks later when I needed to add another IF/AND statement with index-matching. Then the whole thing wouldn't even run because Excel thought I had too many arguments for the function.
So, this weekend I was putzing around with the formula, and I started parsing out bits and realized this IF/AND statement was only partially working.
Here's what I mean:
(my formula is in the Priority column (col C) of sheet 1)
If the STATUS(col. B) says Approved, I want my formula to index-match the priority from sheet 2, unless that cell is blank - then I want the priority on sheet 1 to say "Medium." If Status on sheet 1 doesn't say Approved, then I don't want my formula to do anything in that cell.
=IF((AND(B2="Approved",INDEX(Sheet2!B:B,MATCH(Sheet1!A2,Sheet2!A:A,0))="")),"Medium", INDEX(Sheet2!B:B,MATCH(Sheet1!A2,Sheet2!A:A,0)))
which goes in in col. C of sheet1:
Here is sheet 2:
Item 10 works well - Status is Approved and priority on sheet 2 is blank so col. C says "Medium."
But, col C for items 20 and 25 have been index-matched when they shouldn't - they're status is not Approved, so they should be left blank.
I had a couple of ideas about remedying this, but neither worked. One idea was to add an AND(B2="Approved") to the last index-match,
=IF((AND(B2="Approved",INDEX(Sheet2!B:B,MATCH(Sheet1!A2,Sheet2!A:A,0))="")),"Medium", (AND(B2="Approved",INDEX(Sheet2!B:B,MATCH(Sheet1!A2,Sheet2!A:A,0)))))
Which just returns a bunch of TRUE or FALSE.
I also tinkered with adding another IF into that last index-match bit, but I realized that it wouldn't be one cohesive IF statement anymore.
How can I change my formula to get it to work? Sorry if the answer is obvious, I'm pretty new to Excel.
microsoft-excel worksheet-function microsoft-excel-2010 microsoft-excel-2007
asked 8 hours ago
forlornforlorn
103
add a comment |
So I had a very long nested-if statement, and the last nested-IF was an IF/AND statement with index matching. The formula worked fine (or so I thought), until a few weeks later when I needed to add another IF/AND statement with index-matching. Then the whole thing wouldn't even run because Excel thought I had too many arguments for the function.
So, this weekend I was putzing around with the formula, and I started parsing out bits and realized this IF/AND statement was only partially working.
Here's what I mean:
(my formula is in the Priority column (col C) of sheet 1)
If the STATUS(col. B) says Approved, I want my formula to index-match the priority from sheet 2, unless that cell is blank - then I want the priority on sheet 1 to say "Medium." If Status on sheet 1 doesn't say Approved, then I don't want my formula to do anything in that cell.
=IF((AND(B2="Approved",INDEX(Sheet2!B:B,MATCH(Sheet1!A2,Sheet2!A:A,0))="")),"Medium", INDEX(Sheet2!B:B,MATCH(Sheet1!A2,Sheet2!A:A,0)))
which goes in in col. C of sheet1:
Here is sheet 2:
Item 10 works well - Status is Approved and priority on sheet 2 is blank so col. C says "Medium."
But, col C for items 20 and 25 have been index-matched when they shouldn't - they're status is not Approved, so they should be left blank.
I had a couple of ideas about remedying this, but neither worked. One idea was to add an AND(B2="Approved") to the last index-match,
=IF((AND(B2="Approved",INDEX(Sheet2!B:B,MATCH(Sheet1!A2,Sheet2!A:A,0))="")),"Medium", (AND(B2="Approved",INDEX(Sheet2!B:B,MATCH(Sheet1!A2,Sheet2!A:A,0)))))
Which just returns a bunch of TRUE or FALSE.
I also tinkered with adding another IF into that last index-match bit, but I realized that it wouldn't be one cohesive IF statement anymore.
How can I change my formula to get it to work? Sorry if the answer is obvious, I'm pretty new to Excel.
microsoft-excel worksheet-function microsoft-excel-2010 microsoft-excel-2007
asked 8 hours ago
forlornforlorn
103
So I had a very long nested-if statement, and the last nested-IF was an IF/AND statement with index matching. The formula worked fine (or so I thought), until a few weeks later when I needed to add another IF/AND statement with index-matching. Then the whole thing wouldn't even run because Excel thought I had too many arguments for the function.
So, this weekend I was putzing around with the formula, and I started parsing out bits and realized this IF/AND statement was only partially working.
Here's what I mean:
(my formula is in the Priority column (col C) of sheet 1)
If the STATUS(col. B) says Approved, I want my formula to index-match the priority from sheet 2, unless that cell is blank - then I want the priority on sheet 1 to say "Medium." If Status on sheet 1 doesn't say Approved, then I don't want my formula to do anything in that cell.
=IF((AND(B2="Approved",INDEX(Sheet2!B:B,MATCH(Sheet1!A2,Sheet2!A:A,0))="")),"Medium", INDEX(Sheet2!B:B,MATCH(Sheet1!A2,Sheet2!A:A,0)))
which goes in in col. C of sheet1:
Here is sheet 2:
Item 10 works well - Status is Approved and priority on sheet 2 is blank so col. C says "Medium."
But, col C for items 20 and 25 have been index-matched when they shouldn't - they're status is not Approved, so they should be left blank.
I had a couple of ideas about remedying this, but neither worked. One idea was to add an AND(B2="Approved") to the last index-match,
=IF((AND(B2="Approved",INDEX(Sheet2!B:B,MATCH(Sheet1!A2,Sheet2!A:A,0))="")),"Medium", (AND(B2="Approved",INDEX(Sheet2!B:B,MATCH(Sheet1!A2,Sheet2!A:A,0)))))
Which just returns a bunch of TRUE or FALSE.
I also tinkered with adding another IF into that last index-match bit, but I realized that it wouldn't be one cohesive IF statement anymore.
How can I change my formula to get it to work? Sorry if the answer is obvious, I'm pretty new to Excel.
microsoft-excel worksheet-function microsoft-excel-2010 microsoft-excel-2007
microsoft-excel worksheet-function microsoft-excel-2010 microsoft-excel-2007
asked 8 hours ago
forlornforlorn
103
asked 8 hours ago
forlornforlorn
103
asked 8 hours ago
forlornforlorn
103
asked 8 hours ago
forlornforlorn
103
asked 8 hours ago
forlornforlorn
103
103
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
Test if "Approved" first. If false return "" if true then do the if for the index:
=IF(B2="Approved",IF(INDEX(Sheet2!B:B,MATCH(Sheet1!A2,Sheet2!A:A,0))="","Medium", INDEX(Sheet2!B:B,MATCH(Sheet1!A2,Sheet2!A:A,0)),"")
answered 7 hours ago
Scott CranerScott Craner
12.2k11118
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
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%2fsuperuser.com%2fquestions%2f1411199%2fif-and-statement-with-index-matching%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
Test if "Approved" first. If false return "" if true then do the if for the index:
=IF(B2="Approved",IF(INDEX(Sheet2!B:B,MATCH(Sheet1!A2,Sheet2!A:A,0))="","Medium", INDEX(Sheet2!B:B,MATCH(Sheet1!A2,Sheet2!A:A,0)),"")
answered 7 hours ago
Scott CranerScott Craner
12.2k11118
add a comment |
Test if "Approved" first. If false return "" if true then do the if for the index:
=IF(B2="Approved",IF(INDEX(Sheet2!B:B,MATCH(Sheet1!A2,Sheet2!A:A,0))="","Medium", INDEX(Sheet2!B:B,MATCH(Sheet1!A2,Sheet2!A:A,0)),"")
answered 7 hours ago
Scott CranerScott Craner
12.2k11118
add a comment |
Test if "Approved" first. If false return "" if true then do the if for the index:
=IF(B2="Approved",IF(INDEX(Sheet2!B:B,MATCH(Sheet1!A2,Sheet2!A:A,0))="","Medium", INDEX(Sheet2!B:B,MATCH(Sheet1!A2,Sheet2!A:A,0)),"")
answered 7 hours ago
Scott CranerScott Craner
12.2k11118
Test if "Approved" first. If false return "" if true then do the if for the index:
=IF(B2="Approved",IF(INDEX(Sheet2!B:B,MATCH(Sheet1!A2,Sheet2!A:A,0))="","Medium", INDEX(Sheet2!B:B,MATCH(Sheet1!A2,Sheet2!A:A,0)),"")
answered 7 hours ago
Scott CranerScott Craner
12.2k11118
answered 7 hours ago
Scott CranerScott Craner
12.2k11118
answered 7 hours ago
Scott CranerScott Craner
12.2k11118
answered 7 hours ago
Scott CranerScott Craner
12.2k11118
12.2k11118
add a comment |
add a comment |
Thanks for contributing an answer to Super User!
- 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%2fsuperuser.com%2fquestions%2f1411199%2fif-and-statement-with-index-matching%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
