Index + Match, returning incorrect value, date match required

JackDanIce

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,922
Office Version
  1. 365
Platform
  1. Windows
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:
Code: 
=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:
Code: 
=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>
 
Last edited:
Sort by date Sort by votes
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.
 
Last edited:
Upvote 0

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
JackDanIce said:
Ultimately it will end up in a macro, ..
Click to expand...
Then why not just use vba in the first place? Here is a pretty simple UDF that I believe does what you want.

Code: 
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
EFGHIJKLM
2DateSourceRate
37/04/2003Securities10%10%
4
11
12DateSourceRate
136/04/1990Property18%
146/04/1990Securities10%
156/04/2016Property11%
166/04/2016Securities8%
Sheet1
 
Last edited:
Upvote 0
#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.
 
Upvote 0
JackDanIce said:
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
Click to expand...
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. :cool:
 
Upvote 0
JackDanIce said:
#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.
Click to expand...

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.
[…]
Click to expand...

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.
 
Upvote 0
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​
NameTypeIDValueMap
Jeff VaderCapital GainsCG5
50000​
E
Jeff VaderCapital GainsCG1
400,000​
A
Jeff VaderCapital GainsCG3
200000​
C
Jeff VaderCapital GainsCG2
300,000​
B
Jeff VaderCapital GainsCG4
100000​
D
Key​
Property (non residential)32969
06/04/1990​
Property (non residential)C
Stocks and Shares32969
06/04/1990​
Stocks and SharesA
Property (non residential)42466
06/04/2016​
Property (non residential)
11%​
Stocks and Shares42466
06/04/2016​
Stocks and Shares
8%​
Key​
Property (non residential)32969
06/04/1990​
Property (non residential)E
Stocks and Shares32969
06/04/1990​
Stocks and SharesB
Property (non residential)42466
06/04/2016​
Property (non residential)
20%​
Stocks and Shares42466
06/04/2016​
Stocks and SharesD

<tbody>
[TD="bgcolor: #E0E0F0"]
1​
[/TD]
[TD="bgcolor: #D9D9D9"]
Client​
[/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[TD="bgcolor: #D9D9D9"][/TD]

[TD="bgcolor: #E0E0F0"]
2​
[/TD]

[TD="bgcolor: #660066"] Tax Category [/TD]

[TD="bgcolor: #660066"] Date [/TD]

[TD="bgcolor: #548235"] Gain Source [/TD]
[TD="bgcolor: #806000"]
Rate​
[/TD]

[TD="bgcolor: #E0E0F0"]
3​
[/TD]

[TD="bgcolor: #BFBFBF"]Higher[/TD]

[TD="bgcolor: #BFBFBF"]
01/01/1992​
[/TD]

[TD="bgcolor: #BFBFBF"]Property (non residential)[/TD]
[TD="bgcolor: #BFBFBF"]
28.0%​
[/TD]

[TD="bgcolor: #E0E0F0"]
4​
[/TD]

[TD="bgcolor: #00B050"]Basic[/TD]

[TD="bgcolor: #00B050"]
06/04/2003​
[/TD]

[TD="bgcolor: #00B050"]Stocks and Shares[/TD]
[TD="bgcolor: #00B050"]
10.0%​
[/TD]

[TD="bgcolor: #E0E0F0"]
5​
[/TD]

[TD="bgcolor: #9BC2E6"]Basic[/TD]

[TD="bgcolor: #9BC2E6"]
06/04/2003​
[/TD]

[TD="bgcolor: #9BC2E6"]Property (non residential)[/TD]
[TD="bgcolor: #9BC2E6"]
18.0%​
[/TD]

[TD="bgcolor: #E0E0F0"]
6​
[/TD]

[TD="bgcolor: #FFFF00"]Higher[/TD]

[TD="bgcolor: #FFFF00"]
30/06/2015​
[/TD]

[TD="bgcolor: #FFFF00"]Stocks and Shares[/TD]
[TD="bgcolor: #FFFF00"]
15.0%​
[/TD]

[TD="bgcolor: #E0E0F0"]
7​
[/TD]

[TD="bgcolor: #F4B084"]Higher[/TD]

[TD="bgcolor: #F4B084"]
30/06/2017​
[/TD]

[TD="bgcolor: #F4B084"]Property (non residential)[/TD]
[TD="bgcolor: #F4B084"]
13.0%​
[/TD]

[TD="bgcolor: #E0E0F0"]
8​
[/TD]

[TD="bgcolor: #E0E0F0"]
9​
[/TD]

[TD="bgcolor: #D9D9D9"]
Capital Gains Basic​
[/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[TD="bgcolor: #D9D9D9"][/TD]

[TD="bgcolor: #E0E0F0"]
10​
[/TD]

[TD="bgcolor: #660066"]
Date​
[/TD]
[TD="bgcolor: #548235"] Gain Source [/TD]
[TD="bgcolor: #806000"]
Rate​
[/TD]

[TD="bgcolor: #E0E0F0"]
11​
[/TD]

[TD="bgcolor: #9BC2E6"]
18%​
[/TD]

[TD="bgcolor: #E0E0F0"]
12​
[/TD]

[TD="bgcolor: #00B050"]
10%​
[/TD]

[TD="bgcolor: #E0E0F0"]
13​
[/TD]

[TD="bgcolor: #E0E0F0"]
14​
[/TD]

[TD="bgcolor: #E0E0F0"]
15​
[/TD]

[TD="bgcolor: #E0E0F0"]
16​
[/TD]

[TD="bgcolor: #E0E0F0"]
17​
[/TD]

[TD="bgcolor: #E0E0F0"]
18​
[/TD]

[TD="bgcolor: #E0E0F0"]
19​
[/TD]

[TD="bgcolor: #D9D9D9"]
Capital Gains Higher​
[/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[TD="bgcolor: #D9D9D9"][/TD]

[TD="bgcolor: #E0E0F0"]
20​
[/TD]

[TD="bgcolor: #660066"]
Date​
[/TD]
[TD="bgcolor: #548235"] Gain Source [/TD]
[TD="bgcolor: #806000"]
Rate​
[/TD]

[TD="bgcolor: #E0E0F0"]
21​
[/TD]

[TD="bgcolor: #BFBFBF"]
28%​
[/TD]

[TD="bgcolor: #E0E0F0"]
22​
[/TD]

[TD="bgcolor: #FFFF00"]
15%​
[/TD]

[TD="bgcolor: #E0E0F0"]
23​
[/TD]

[TD="bgcolor: #E0E0F0"]
24​
[/TD]

[TD="bgcolor: #F4B084"]
13%​
[/TD]

[TD="bgcolor: #E0E0F0"]
25​
[/TD]

[TD="bgcolor: #E0E0F0"]
26​
[/TD]

[TD="bgcolor: #E0E0F0"]
27​
[/TD]

</tbody>
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Data[/TD]
[/TR]
</tbody>[/TABLE]

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)
 
Last edited:
Upvote 0
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
 
Upvote 0
JackDanIce said:
Peter, just tested your formula returns the correct rate from the given table, thank you very much ..
Click to expand...
You are welcome. Thanks for the confirmation.


JackDanIce said:
.. and non array too.
Click to expand...
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Create New Sheet with Specific Name Based on Variable Cell
Replies
0
Views
242
SBExcel123
S
K
  • Question Question
Dynamic Address for Use in Function Always returns ERROR
Replies
3
Views
549
Eric W
Eric W
W
  • Solved
TEXTJOIN return UNIQUE values with INDEXMATCH multiple criteria
Replies
2
Views
1K
WhimsicalRaptor87
W
J
  • Solved
returning a value if multiple criteria are true
Replies
3
Views
405
etaf
etaf
ExceLoki
  • Solved
Match name and return sum between date range
2
Replies
15
Views
910
ExceLoki
ExceLoki

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top