Microsoft Excel: How to pick the correct cell based on a different column?











up vote
0
down vote

favorite
1












Update:



I've managed to get Excel to pick a rate based on what date range it falls under.



I now need something (in combination with my new formula below)to only pick those rates according to the currency.
So If I have two exchange rates, EUR and GBP with the same date range of 01/05/18 to 20/05/18. And I made a payment in GBP, I need it to select the GBP rate.
As you can see below, my formula is picking 1.2, which is the EUR rate and not GBP.



Here is my updated formula:



=LOOKUP(2,1/($B$94:$B$95<=C99)/($C$94:$C$95>=C99),$G$94:$G$95)


I have simplified my screenshots I initial added for the convenience of the viewers:



enter image description here



Initial Question:



I'm dealing with accounting dates, where the new month may start on 26th for example.



Currently, I have formula set up to pick a exchange rate based on what month a payment was made.
So if I paid something on May, excel will get the exchange rate from May.



But I now need it to pick it based on a specific date.
So if I made a payment on the 26th of May, but the account month start on the 20th of May, I need it to pick the June currency.



I'm not quite sure how to do this, maybe, two ifs statements <=01/05/18 and >=20/05/18.
Or perhaps, create a range of dates in once cell?



Current formula:




=SUMIFS('Exchange Rates'!F9:F1378, table of exchange rates
'Exchange Rates'!B9:B1378,L563, picks up what currency to use EUR or GBP
'Exchange Rates'!D9:D1378,TEXT(J563,"yyyymm")) picks up what date to use




Formula under Col U



Exchange rate list










share|improve this question




















  • 1




    From the tables how do you know the account month started on the 20th? The Book Period column only has the year and month in it.
    – Darren Bartrup-Cook
    Nov 8 at 11:59










  • Sorry, should have been clearer. So I have the date ranges, but I'm not sure how I should be inputting them in the Book Period. Currently, that's how the table sits
    – Sat10
    Nov 8 at 12:13

















up vote
0
down vote

favorite
1












Update:



I've managed to get Excel to pick a rate based on what date range it falls under.



I now need something (in combination with my new formula below)to only pick those rates according to the currency.
So If I have two exchange rates, EUR and GBP with the same date range of 01/05/18 to 20/05/18. And I made a payment in GBP, I need it to select the GBP rate.
As you can see below, my formula is picking 1.2, which is the EUR rate and not GBP.



Here is my updated formula:



=LOOKUP(2,1/($B$94:$B$95<=C99)/($C$94:$C$95>=C99),$G$94:$G$95)


I have simplified my screenshots I initial added for the convenience of the viewers:



enter image description here



Initial Question:



I'm dealing with accounting dates, where the new month may start on 26th for example.



Currently, I have formula set up to pick a exchange rate based on what month a payment was made.
So if I paid something on May, excel will get the exchange rate from May.



But I now need it to pick it based on a specific date.
So if I made a payment on the 26th of May, but the account month start on the 20th of May, I need it to pick the June currency.



I'm not quite sure how to do this, maybe, two ifs statements <=01/05/18 and >=20/05/18.
Or perhaps, create a range of dates in once cell?



Current formula:




=SUMIFS('Exchange Rates'!F9:F1378, table of exchange rates
'Exchange Rates'!B9:B1378,L563, picks up what currency to use EUR or GBP
'Exchange Rates'!D9:D1378,TEXT(J563,"yyyymm")) picks up what date to use




Formula under Col U



Exchange rate list










share|improve this question




















  • 1




    From the tables how do you know the account month started on the 20th? The Book Period column only has the year and month in it.
    – Darren Bartrup-Cook
    Nov 8 at 11:59










  • Sorry, should have been clearer. So I have the date ranges, but I'm not sure how I should be inputting them in the Book Period. Currently, that's how the table sits
    – Sat10
    Nov 8 at 12:13















up vote
0
down vote

favorite
1









up vote
0
down vote

favorite
1






1





Update:



I've managed to get Excel to pick a rate based on what date range it falls under.



I now need something (in combination with my new formula below)to only pick those rates according to the currency.
So If I have two exchange rates, EUR and GBP with the same date range of 01/05/18 to 20/05/18. And I made a payment in GBP, I need it to select the GBP rate.
As you can see below, my formula is picking 1.2, which is the EUR rate and not GBP.



Here is my updated formula:



=LOOKUP(2,1/($B$94:$B$95<=C99)/($C$94:$C$95>=C99),$G$94:$G$95)


I have simplified my screenshots I initial added for the convenience of the viewers:



enter image description here



Initial Question:



I'm dealing with accounting dates, where the new month may start on 26th for example.



Currently, I have formula set up to pick a exchange rate based on what month a payment was made.
So if I paid something on May, excel will get the exchange rate from May.



But I now need it to pick it based on a specific date.
So if I made a payment on the 26th of May, but the account month start on the 20th of May, I need it to pick the June currency.



I'm not quite sure how to do this, maybe, two ifs statements <=01/05/18 and >=20/05/18.
Or perhaps, create a range of dates in once cell?



Current formula:




=SUMIFS('Exchange Rates'!F9:F1378, table of exchange rates
'Exchange Rates'!B9:B1378,L563, picks up what currency to use EUR or GBP
'Exchange Rates'!D9:D1378,TEXT(J563,"yyyymm")) picks up what date to use




Formula under Col U



Exchange rate list










share|improve this question















Update:



I've managed to get Excel to pick a rate based on what date range it falls under.



I now need something (in combination with my new formula below)to only pick those rates according to the currency.
So If I have two exchange rates, EUR and GBP with the same date range of 01/05/18 to 20/05/18. And I made a payment in GBP, I need it to select the GBP rate.
As you can see below, my formula is picking 1.2, which is the EUR rate and not GBP.



Here is my updated formula:



=LOOKUP(2,1/($B$94:$B$95<=C99)/($C$94:$C$95>=C99),$G$94:$G$95)


I have simplified my screenshots I initial added for the convenience of the viewers:



enter image description here



Initial Question:



I'm dealing with accounting dates, where the new month may start on 26th for example.



Currently, I have formula set up to pick a exchange rate based on what month a payment was made.
So if I paid something on May, excel will get the exchange rate from May.



But I now need it to pick it based on a specific date.
So if I made a payment on the 26th of May, but the account month start on the 20th of May, I need it to pick the June currency.



I'm not quite sure how to do this, maybe, two ifs statements <=01/05/18 and >=20/05/18.
Or perhaps, create a range of dates in once cell?



Current formula:




=SUMIFS('Exchange Rates'!F9:F1378, table of exchange rates
'Exchange Rates'!B9:B1378,L563, picks up what currency to use EUR or GBP
'Exchange Rates'!D9:D1378,TEXT(J563,"yyyymm")) picks up what date to use




Formula under Col U



Exchange rate list







excel if-statement excel-formula match vlookup






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 9 at 11:13

























asked Nov 8 at 11:49









Sat10

6619




6619








  • 1




    From the tables how do you know the account month started on the 20th? The Book Period column only has the year and month in it.
    – Darren Bartrup-Cook
    Nov 8 at 11:59










  • Sorry, should have been clearer. So I have the date ranges, but I'm not sure how I should be inputting them in the Book Period. Currently, that's how the table sits
    – Sat10
    Nov 8 at 12:13
















  • 1




    From the tables how do you know the account month started on the 20th? The Book Period column only has the year and month in it.
    – Darren Bartrup-Cook
    Nov 8 at 11:59










  • Sorry, should have been clearer. So I have the date ranges, but I'm not sure how I should be inputting them in the Book Period. Currently, that's how the table sits
    – Sat10
    Nov 8 at 12:13










1




1




From the tables how do you know the account month started on the 20th? The Book Period column only has the year and month in it.
– Darren Bartrup-Cook
Nov 8 at 11:59




From the tables how do you know the account month started on the 20th? The Book Period column only has the year and month in it.
– Darren Bartrup-Cook
Nov 8 at 11:59












Sorry, should have been clearer. So I have the date ranges, but I'm not sure how I should be inputting them in the Book Period. Currently, that's how the table sits
– Sat10
Nov 8 at 12:13






Sorry, should have been clearer. So I have the date ranges, but I'm not sure how I should be inputting them in the Book Period. Currently, that's how the table sits
– Sat10
Nov 8 at 12:13














1 Answer
1






active

oldest

votes

















up vote
2
down vote



accepted










I would just combine all criteria in single SUMIFS() (quite similar to what you did initially) :



=SUMIFS($D$1:$D$2;$A$1:$A$2;"<="&A4;$B$1:$B$2;">="&A4;$C$1:$C$2;B4)



Based on the following reduced data-set (starting in cell A1, with the formula in D4) :



20-Sep-18   25-Oct-18   GBP     1.15
20-Sep-18 25-Oct-18 EUR 1.2


25-Sep-18 GBP 1.15





share|improve this answer























  • Thanks Pete, worked perfectly!!!
    – Sat10
    Nov 9 at 11:54










  • @Sat10 You're welcome. Please note that it's a very "bare" formula, without any error handling, so you have to be quite sure about the quality of your dataset. If the conditions are not met, the result will be 0, but if you would have two rows that match the criteria (e.g. because the 'Date From' and 'Date End' have some overlap) the result would be of course the sum of the two rates, which is rather a big mistake ;). You could mitigate this by using AVERAGEIFS(), or you can wrap everything with a COUNTIFS() to have more control over what should happen in such cases.
    – Peter K.
    Nov 9 at 12:06












  • Okay, thanks for the heads up on this.
    – Sat10
    Nov 9 at 14:19











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',
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
});


}
});














 

draft saved


draft discarded


















StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53207158%2fmicrosoft-excel-how-to-pick-the-correct-cell-based-on-a-different-column%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








up vote
2
down vote



accepted










I would just combine all criteria in single SUMIFS() (quite similar to what you did initially) :



=SUMIFS($D$1:$D$2;$A$1:$A$2;"<="&A4;$B$1:$B$2;">="&A4;$C$1:$C$2;B4)



Based on the following reduced data-set (starting in cell A1, with the formula in D4) :



20-Sep-18   25-Oct-18   GBP     1.15
20-Sep-18 25-Oct-18 EUR 1.2


25-Sep-18 GBP 1.15





share|improve this answer























  • Thanks Pete, worked perfectly!!!
    – Sat10
    Nov 9 at 11:54










  • @Sat10 You're welcome. Please note that it's a very "bare" formula, without any error handling, so you have to be quite sure about the quality of your dataset. If the conditions are not met, the result will be 0, but if you would have two rows that match the criteria (e.g. because the 'Date From' and 'Date End' have some overlap) the result would be of course the sum of the two rates, which is rather a big mistake ;). You could mitigate this by using AVERAGEIFS(), or you can wrap everything with a COUNTIFS() to have more control over what should happen in such cases.
    – Peter K.
    Nov 9 at 12:06












  • Okay, thanks for the heads up on this.
    – Sat10
    Nov 9 at 14:19















up vote
2
down vote



accepted










I would just combine all criteria in single SUMIFS() (quite similar to what you did initially) :



=SUMIFS($D$1:$D$2;$A$1:$A$2;"<="&A4;$B$1:$B$2;">="&A4;$C$1:$C$2;B4)



Based on the following reduced data-set (starting in cell A1, with the formula in D4) :



20-Sep-18   25-Oct-18   GBP     1.15
20-Sep-18 25-Oct-18 EUR 1.2


25-Sep-18 GBP 1.15





share|improve this answer























  • Thanks Pete, worked perfectly!!!
    – Sat10
    Nov 9 at 11:54










  • @Sat10 You're welcome. Please note that it's a very "bare" formula, without any error handling, so you have to be quite sure about the quality of your dataset. If the conditions are not met, the result will be 0, but if you would have two rows that match the criteria (e.g. because the 'Date From' and 'Date End' have some overlap) the result would be of course the sum of the two rates, which is rather a big mistake ;). You could mitigate this by using AVERAGEIFS(), or you can wrap everything with a COUNTIFS() to have more control over what should happen in such cases.
    – Peter K.
    Nov 9 at 12:06












  • Okay, thanks for the heads up on this.
    – Sat10
    Nov 9 at 14:19













up vote
2
down vote



accepted







up vote
2
down vote



accepted






I would just combine all criteria in single SUMIFS() (quite similar to what you did initially) :



=SUMIFS($D$1:$D$2;$A$1:$A$2;"<="&A4;$B$1:$B$2;">="&A4;$C$1:$C$2;B4)



Based on the following reduced data-set (starting in cell A1, with the formula in D4) :



20-Sep-18   25-Oct-18   GBP     1.15
20-Sep-18 25-Oct-18 EUR 1.2


25-Sep-18 GBP 1.15





share|improve this answer














I would just combine all criteria in single SUMIFS() (quite similar to what you did initially) :



=SUMIFS($D$1:$D$2;$A$1:$A$2;"<="&A4;$B$1:$B$2;">="&A4;$C$1:$C$2;B4)



Based on the following reduced data-set (starting in cell A1, with the formula in D4) :



20-Sep-18   25-Oct-18   GBP     1.15
20-Sep-18 25-Oct-18 EUR 1.2


25-Sep-18 GBP 1.15






share|improve this answer














share|improve this answer



share|improve this answer








edited Nov 9 at 11:41

























answered Nov 9 at 11:34









Peter K.

3198




3198












  • Thanks Pete, worked perfectly!!!
    – Sat10
    Nov 9 at 11:54










  • @Sat10 You're welcome. Please note that it's a very "bare" formula, without any error handling, so you have to be quite sure about the quality of your dataset. If the conditions are not met, the result will be 0, but if you would have two rows that match the criteria (e.g. because the 'Date From' and 'Date End' have some overlap) the result would be of course the sum of the two rates, which is rather a big mistake ;). You could mitigate this by using AVERAGEIFS(), or you can wrap everything with a COUNTIFS() to have more control over what should happen in such cases.
    – Peter K.
    Nov 9 at 12:06












  • Okay, thanks for the heads up on this.
    – Sat10
    Nov 9 at 14:19


















  • Thanks Pete, worked perfectly!!!
    – Sat10
    Nov 9 at 11:54










  • @Sat10 You're welcome. Please note that it's a very "bare" formula, without any error handling, so you have to be quite sure about the quality of your dataset. If the conditions are not met, the result will be 0, but if you would have two rows that match the criteria (e.g. because the 'Date From' and 'Date End' have some overlap) the result would be of course the sum of the two rates, which is rather a big mistake ;). You could mitigate this by using AVERAGEIFS(), or you can wrap everything with a COUNTIFS() to have more control over what should happen in such cases.
    – Peter K.
    Nov 9 at 12:06












  • Okay, thanks for the heads up on this.
    – Sat10
    Nov 9 at 14:19
















Thanks Pete, worked perfectly!!!
– Sat10
Nov 9 at 11:54




Thanks Pete, worked perfectly!!!
– Sat10
Nov 9 at 11:54












@Sat10 You're welcome. Please note that it's a very "bare" formula, without any error handling, so you have to be quite sure about the quality of your dataset. If the conditions are not met, the result will be 0, but if you would have two rows that match the criteria (e.g. because the 'Date From' and 'Date End' have some overlap) the result would be of course the sum of the two rates, which is rather a big mistake ;). You could mitigate this by using AVERAGEIFS(), or you can wrap everything with a COUNTIFS() to have more control over what should happen in such cases.
– Peter K.
Nov 9 at 12:06






@Sat10 You're welcome. Please note that it's a very "bare" formula, without any error handling, so you have to be quite sure about the quality of your dataset. If the conditions are not met, the result will be 0, but if you would have two rows that match the criteria (e.g. because the 'Date From' and 'Date End' have some overlap) the result would be of course the sum of the two rates, which is rather a big mistake ;). You could mitigate this by using AVERAGEIFS(), or you can wrap everything with a COUNTIFS() to have more control over what should happen in such cases.
– Peter K.
Nov 9 at 12:06














Okay, thanks for the heads up on this.
– Sat10
Nov 9 at 14:19




Okay, thanks for the heads up on this.
– Sat10
Nov 9 at 14:19


















 

draft saved


draft discarded



















































 


draft saved


draft discarded














StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53207158%2fmicrosoft-excel-how-to-pick-the-correct-cell-based-on-a-different-column%23new-answer', 'question_page');
}
);

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







Popular posts from this blog

Guess what letter conforming each word

Port of Spain

Run scheduled task as local user group (not BUILTIN)