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>

 

[JackDanIce]

Thank you, apologies for any confusion. Anything prior to date of 2016-04-06 (so 2016-04-05 or earlier) should return rate for earlier date of 1990-06-04 because in table that was the last date before 2016-04-06 to have a rate value.

If you consider choronlogical order, the dates of new rates are like country interest rate changes so you'd return the most recent stated one until a new (later) date showed change.

This is where I'm having any "MATCH" function return incorrect row because (I think) matching dates for different sources isn't distinguishable easily via spreadsheet formula* hence this unusual date switch across the 6th and 7th days of 2003-04-06 and 2003-04-07 relates to either rate of 1990 or 2016 respectively when any date prior 2016-04-06 should return 1990's rate (and I hope to be incorrect about this)

There may also be issues with system settings but this macro is expected to only run on PCs operating with UK settings

*variables are only date, type (property) to return related rate, where date matched to most recent one past

If there is any confusion I apologise and please state where so I can clarify.

 

[Aladin Akyurek]

Sorry, I don't follow. I was just asking about the percentages which must obtain for 2003-04-06 and for 2003-04-07.

 

[Peter_SSs]

Ultimately it will end up in a macro, ..

Then why not just use vba in the first place? Here is a pretty simple UDF that I believe does what you want.

Function GetRate(rData As Range, dDate As Date, sSource As String) As Variant
  Dim a As Variant
  Dim i As Long
  
  a = rData.Value
  GetRate = "Not Found"
  i = UBound(a)
  Do
    If a(i, 2) = sSource And a(i, 1) <= dDate Then GetRate = a(i, 3)
    i = i - 1
  Loop Until IsNumeric(GetRate) Or i = 0
End Function

If you do really want a worksheet formula, try the one in J3

Excel Workbook
	E	F	G	H	I	J	K	L	M
2	Date		Source		Rate
3	7/04/2003		Securities		10%	10%
4
11
12							Date	Source	Rate
13							6/04/1990	Property	18%
14							6/04/1990	Securities	10%
15							6/04/2016	Property	11%
16							6/04/2016	Securities	8%
Sheet1

 

[JackDanIce]

#12 Hi Aladin sorry, any date prior to 2016-04-06 should return the rate of 18% because the last stated rate date was 1990-06-04.

What I meant is with input of 2003-04-06 your suggestion was returning 1990 rate and for 2003-04-07 it is returning rate for 2016-04-06. It is not clear why for an input of 2003-04-07 it is returning 2016's rate and NOT 1990's rate as relative to 2003-04-07, 2016 is in the future so formula shouldn't return 2016's rate when input is before this date.

Hi Peter_SSs

I have to create a mock/model first without VBA. This is client's call and just one part of the model's calculations.

They need to be satisfied the output is correct in a manner they understand, usually there is some madness to me asking a question on here! Plus, with respect, if I were going straight to VBA, I would have designed a similar Private UDF to return the rate, although saves me some coding then, thank you

It's trying to avoid putting extra unecessary detail in as well, apologise if post has been too succient

I will try suggested formula later this morning, thank you, do appreciate suggestion.

 

[Peter_SSs]

Hi Peter_SSs

I have to create a mock/model first without VBA. This is client's call and just one part of the model's calculations.

They need to be satisfied the output is correct

Fair enough, client rules, & I'm not trying to get into an argument about it but it doesn't make a lot of sense to me. Once they are satisfied that a formula returns the correct result and you then turn that into vba, they have to again satisfy themselves themselves that the results are correct (or at least they should) in case the vba implementation was not correct.

 

[Aladin Akyurek]

#12 Hi Aladin sorry, any date prior to 2016-04-06 should return the rate of 18% because the last stated rate date was 1990-06-04.

What I meant is with input of 2003-04-06 your suggestion was returning 1990 rate and for 2003-04-07 it is returning rate for 2016-04-06.

Is this a general rule or specific to the data you posted?

It is not clear why for an input of 2003-04-07 it is returning 2016's rate and NOT 1990's rate as relative to 2003-04-07, 2016 is in the future so formula shouldn't return 2016's rate when input is before this date.
[…]

It's better imho to deliver the results which must obtain for a given input and to clarify the rule which must govern those results, instead of discussing the behavior of a (suggested) formula.

And thanks much for the update.

 

[JackDanIce]

Hello both, hope this clarifies with colours. The date format is DD/MM/YYYY (to avoid any ambiguity) in the image below

Excel 2016 (Windows) 32 bit

	A​	B​	C​	D​	E​	F​	G​	H​	I​
1​	Client​
2​	Name	Tax Category	Type	ID	Date	Value	Gain Source	Rate​	Map
3​	Jeff Vader	Higher	Capital Gains	CG5	01/01/1992​	50000​	Property (non residential)	28.0%​	E
4​	Jeff Vader	Basic	Capital Gains	CG1	06/04/2003​	400,000​	Stocks and Shares	10.0%​	A
5​	Jeff Vader	Basic	Capital Gains	CG3	06/04/2003​	200000​	Property (non residential)	18.0%​	C
6​	Jeff Vader	Higher	Capital Gains	CG2	30/06/2015​	300,000​	Stocks and Shares	15.0%​	B
7​	Jeff Vader	Higher	Capital Gains	CG4	30/06/2017​	100000​	Property (non residential)	13.0%​	D
8​
9​					Capital Gains Basic​
10​					Key​	Date​	Gain Source	Rate​
11​					Property (non residential)32969	06/04/1990​	Property (non residential)	18%​	C
12​					Stocks and Shares32969	06/04/1990​	Stocks and Shares	10%​	A
13​					Property (non residential)42466	06/04/2016​	Property (non residential)	11%​
14​					Stocks and Shares42466	06/04/2016​	Stocks and Shares	8%​
15​
16​
17​
18​
19​					Capital Gains Higher​
20​					Key​	Date​	Gain Source	Rate​
21​					Property (non residential)32969	06/04/1990​	Property (non residential)	28%​	E
22​					Stocks and Shares32969	06/04/1990​	Stocks and Shares	15%​	B
23​					Property (non residential)42466	06/04/2016​	Property (non residential)	20%​
24​					Stocks and Shares42466	06/04/2016​	Stocks and Shares	13%​	D
25​
26​
27​

<tbody>
</tbody>

Sheet: Data

<tbody>
</tbody>

Date format used below for clarity is DD/MMM/YYYY, so that month abbreviations are separate to day numbers

I'm trying to map from the Capital Gains table (rows 9 and below) to the Client, specifically H3:H7

Column I shows which row I am returning rates from for the required output. The date is the main differentiator, where the client date must be >= the most recent (historical) date in the Capital Gains table.

So any client date upto 05/APR/2016 must return rates for 06/APR/1990 because at that point in time, rates for 06/APR/2016 is not known.

If client date is on or after 06/APR/2016 then it must return rates for 06/APR/2016, the source further defines which rate to return.

Inputs: DATE and SOURCE (ignoring tax category). Output rate based on the date (like a ROUNDDOWN because future rates are not known until the client date exceeds those in the capital rates tables) against appropriate source

Two tables because originally had a single column denoting Basic or Higher but just to get this formula working have seperated out. I tried to create a VLOOKUP key of SOURCE & DATE in the Capital Gains tables but this also didn't return the correct values to range: H3:H9

This is all the information I have relating to the formula I'd like to use. If it's still not clear, I will try again, please state what needs explaining.

NB Capital Gains will always be in date order (older - latest)

 

[Peter_SSs]

.. please state what needs explaining.

The result of this:

I will try suggested formula later this morning,..

 

[JackDanIce]

Peter, just tested your formula returns the correct rate from the given table, thank you very much and non array too.

Any client date prior to 2016 Apr 06 returns the "older" rate. Any client date on or after 2016 Apr 06 returns the "updated" rate.

I'm going to recombine the basic and higher table and see if I can adapt your formula or use some IF statement with named ranges to switch accordingly.

Thank you both, sorry for any misunderstanding

 

[Peter_SSs]

Peter, just tested your formula returns the correct rate from the given table, thank you very much ..

You are welcome. Thanks for the confirmation.

.. and non array too.

Well, it actually is an array form of the function, it is just that it doesn't require the C+S+E entry.


Hopefully you can adapt it to suit your needs.