CODEWITHSUNDEEP
excelif-statementexcel-formulamatchvlookup
Sat10
Sat10

Reputation: 111

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

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

Upvotes: 0

Views: 363

Answers (1)

Peter K.
Peter K.

Reputation: 960

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

Upvotes: 2

Related Questions