index match - 2 criteria, one approximate

**abhishek007** · 04-11-2015, 08:58 AM

Hello All,

This forum has helped me a lot to find solutions - however a bit stumped on this one.

Attached is a sheet with Country, Currency, effective date and operational rate.

I was looking to do an index match to find the exchange rate for a given currency and trade date (find the previous closest effective date - if this does not exist then find the closest date after the trade date. (Cells G5:I5)

I something like this formula to get the closest previous date - but it does not take into account the currency code:
=INDEX(E6:E19999, MATCH(MIN(IF(D6:D19999-H6<=0, D6:D19999-H6, "")), D6:D19999-H6, 0))

Thanks!

**newdoverman** · 04-11-2015, 09:37 AM

All the Effective Dates in your workbook are TEXT dates and have to be changed to real Excel Dates. Select column D and use Text to Columns, Delimited, choose Date, MDY, and click Finish. This will convert all the dates to real dates.

Array Enter (Ctrl + Shift + Enter) this formula in H6:

Formula:

Please Login or Register  to view this content.

Array Enter this formula in I6:

Formula:

Please Login or Register  to view this content.

This will give you the last Effective Date for the currency in G6 and the Operational Rate for that date.

**abhishek007** · 04-11-2015, 10:32 AM

Thanks for your reply- In column H6 i already have a list of trade dates. I wanted to to get the closest (last) corresponding operational rate. E.g. For a trade on 17 Dec 2014 with currency CHF (I would like a formula that would find the 15 dec CHF rate of 0.969)

In case the previous date was not available (e.g. for the currency Code SAR, if there is a trade date of 17 Dec 2014, the SAR only has one effective date of 1 april 2015 with rate 3.751) so ideally the formula can pick this one even if it is not the closest previous date.

**newdoverman** · 04-11-2015, 11:45 AM

So, you can pick any date, not necessarily the last but any date at all, and you want the closest one if it isn't exact. Is that correct?

**newdoverman** · 04-11-2015, 11:56 AM

This might require VBA to solve. Right now, I'm just trying to get my head around what you need.

**abhishek007** · 04-11-2015, 12:25 PM

yes exactly right. pick any date, and the formula picks the closest previous date if it exists for the currency code in question.

**abhishek007** · 04-11-2015, 12:29 PM

essentially certain currencies have been traded in the market on different dates (trade dates) and i would like to compare them to the nearest previous UN rate).

initially i had done an index match based on month year and currency. But some currencies have a couple of different rates in one month.

**bebo021999** · 04-11-2015, 02:00 PM

All dates are not in standard date of Excel format, so I have to try to solve it inside the formula, so sorry for such huge formula!

At least, try to delete cell H6, then format cell as "mm/dd/yyyy", the enter below array formula in I6:

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

**abhishek007** · 04-11-2015, 04:19 PM

Long formula but its working well! thank you so much!

**newdoverman** · 04-11-2015, 05:37 PM

Nice formula Quan PT

Here is your example file with Quan PT's formula simplified by using real dates in column D thus eliminating the need to convert the dates for each row.

The dates in column D were converted to real Excel dates by selecting column D and the using Text to Columns, Delimited, Next, Next,DATE (MDY), Finish. Then with the column D selected, format the column as dd/mm/yyyy.

The formula is then shortened to:

Formula:

Please Login or Register  to view this content.

**abhishek007** · 04-11-2015, 05:44 PM

Working well thank you both for the help!

**newdoverman** · 04-11-2015, 05:46 PM

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

index match - 2 criteria, one approximate

LinkBack

Thread Tools

Rate This Thread

Display

index match - 2 criteria, one approximate

Re: index match - 2 criteria, one approximate

Re: index match - 2 criteria, one approximate

Re: index match - 2 criteria, one approximate

Re: index match - 2 criteria, one approximate

Re: index match - 2 criteria, one approximate

Re: index match - 2 criteria, one approximate

Re: index match - 2 criteria, one approximate

Re: index match - 2 criteria, one approximate

Re: index match - 2 criteria, one approximate

Re: index match - 2 criteria, one approximate

Re: index match - 2 criteria, one approximate

Thread Information

Users Browsing this Thread

Similar Threads

Index(Match) with 2 criteria, one of which is approximate

[SOLVED] INDEX & MATCH for approximate matches or combine VLOOKUP & HLOOKUP

[SOLVED] Combining 3 Formulas: Return all Names that Match Criteria Using Index/Match

Formula needed for partial string match as part of index match with multiple criteria

Problems with multi-criteria Index/Match. LOOKUP works. Index/Match, SUMPRODUCT,ARRAY fail

Bookmarks

Bookmarks

Posting Permissions