Index + Match, returning incorrect value, date match required

[JackDanIce]

Hi,

I have a table as follows:

Date Source Rate
06/04/1990 Property 18%
06/04/1990 Securities 10%
06/04/2016 Property 11%
06/04/2016 Securities 8%

A client provides values of date and source and I need to return the appropriate rate where client date is >= nearest date match in table, against the specific source

If A2 has a value of 30/03/2018, then this returns 4:

=MATCH(A2,$K$12:$K$16,1)

but source is "Property", so need to return 3, but unsure how

I've tried to use VLOOKUP as:

=VLOOKUP(A2&B2,$J$12,$K$16,2,1)

Where J12:J16 is a key of <date>&<source> but in this example it returns 18% instead of 11%, despite A2 being closer to 06/04/2016 than 06/04/1990

Any ideas how to return the correct rate based on input of date and source?

TIA,
Jack</date>

 

[Aladin Akyurek]

In C2 control+shift+enter, not just enter:

=INDEX(rates,MATCH(MIN(ABS(IF(properties=B2,dates,9.99E+307)-A2)),ABS(IF(properties=B2,dates,9.99E+307)-A2),0))

where A2 is 2018-03-30 and B2 property. The names dates, properties, and rates should be obvious.

 

[JackDanIce]

Thank you @Aladin Akyurek, will test shortly. Inputs I can adjust for, is there a non array-formula equivalent? Ultimately it will end up in a macro, this is just proofing concepts out so will use array formula if not possible in non-array way.

For disclosure, I did ask this question earlier via using SUMPRODUCT before starting this thread and have referenced both your name and this thread in that now. Apologies if any violations and please close invalid thread accordingly: https://www.mrexcel.com/forum/excel...able-use-sumproduct-return-correct-value.html

 

[Aladin Akyurek]

I think you should test first to see whether it meets your expectations.

 

[JackDanIce]

Just tested, when input date is >= 07/04/2013 it is returning rates for 06/04/2016 rather than 06/04/1990

(General format, tried =07/04/2013 - 06/04/1990 returns 8402 whilst = 06/04/2016 - 07/04/2013 returns 1095)

Source Securities
Date 06/04/2003
Outpupt: 10% (correct as it is matched against rate for 06/04/1990 07/04/2003 < 06/04/2016)

Source Securities
Date 07/04/2003
Outpupt: 8% (incorrect as it is matched against rate for 06/04/2016, but 07/04/2003 < 06/04/2016)

Source Property, same output issue when date changes across 07/04/2003

Formula used:

{=INDEX($M$13:$M$16,MATCH(MIN(ABS(IF($L$13:$L$16=G3,$K$13:$K$16,9.99E+307)-E3)),ABS(IF($L$13:$L$16=G3,$K$13:$K$16,9.99E+307)-E3),0))}

 

[Aladin Akyurek]

Try to describe the dates as: yyyy-mm-dd. This way it'is okay for both UK/European and American systems.

 

[JackDanIce]

Do you mean enter all dates yyyy-mm-dd?

Hmm that's going to be tricky this projects relates specifically to a UK tax advisor and User input is likely to be UK format (used in UK only) and other systems will feed data in, likely UK date format.

Still unsure why rates (output) switches across date 7 April 2003 with earlier dates returning rate for 6 April 1990 otherwise returning "future" date of 6 April 2016...

 

[Aladin Akyurek]

Do you mean enter all dates yyyy-mm-dd? […]

No. Whatever the original format (UK or USA), use the general syntax to talk about it, yyyy-mm-dd. That is:

If UK and you want to say that you have 30/03/2014 say in E2, state here that you have 2014-03-30 for your audience.

If USA and you want to say that you have 3/30/2014 say in E2, state here that you have 2014-03-30 for your audience.

 

[JackDanIce]

Thank you for replying. I didn't realise I needed to specify the data format and wrongly assumed both being from Hague and London respectfully we'd be meaning European dates. My bad.

For the switch date where it's picking up the incorrect rate, dates used in YYYY MM DD format is:
2003 04 06 return earlier rate for 6 April 1990 (sorry April is meant to be 04 so 1990 04 06)
2003 04 07 returns rate for 6 April 2016 (same, April representing 04 so 2016 04 06)

Hope this clarifies any ambiguity unresolved for audience.

 

[Aladin Akyurek]

Thank you for replying. I didn't realise I needed to specify the data format and wrongly assumed both being from Hague and London respectfully we'd be meaning European dates. My bad.

For the switch date where it's picking up the incorrect rate, dates used in YYYY MM DD format is:
2003 04 06 return earlier rate for 6 April 1990 (sorry April is meant to be 04 so 1990 04 06)
2003 04 07 returns rate for 6 April 2016 (same, April representing 04 so 2016 04 06)

Hope this clarifies any ambiguity unresolved for audience.

We get currently 18% for 2003-04-06 and 11% for 2003-04-07 with the suggested formula.
Apparently, these percentages are incorrect. What should be the correct values?