(V) Lookup NEXT (date-wise) available Value from table of values against a calendar which has vacant Values (eg at weekends bank & other holidays).

bornerways

New Member
Joined
Mar 12, 2021
Messages
2
Office Version
  1. 2007
Platform
  1. Windows
I'm an 84 year old 'user' of MS Office 2007, Excel on a Windows 10 platform.
I' normally use VLOOKUP to calculate transactions and values based on the Stock Exchange quoted unit prices for Funds & Shares as published by the FT. These are in a vertical table against a calendar (all dates in a year). Stock Exchanges, wherever, do not normally operate at weekends, bank and other holidays but these vary across the world, so the l table show blanks, sometimes for4 or more days. Transactions, as well, can occur on any of these days so the unit value for the next trading day needs to be used. How, therefore can I look up the NEXT available 'Unit Value' from the tables. Even "Days of the Week" cannot be used as a limiter as Sat becomes a weekday in countries where Friday is their sabbath. A series of nesting fomulae would be difficult to construct & very lengthy,
I can't find a formula that selects the 'NEXT' value or limiter within the MS Excel portfolio which surprises me.
Hope someone can point me towards an easy solution.
Regards
Bornerways
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,432
Office Version
  1. 2016
Platform
  1. Windows
Hi Bornerways,
Can you give an example of your data with a worked example?
XL2BB would be preferred.
 

bornerways

New Member
Joined
Mar 12, 2021
Messages
2
Office Version
  1. 2007
Platform
  1. Windows
I want to calculate the value of a transaction on a specific date
The normal formulae would be 'Transaction Value'(TV)= 'No. of Units Transacted' (N) X 'Unit Price' (UP)
UPs are established on Stock Markets anywhere in the wold -- so on any day and are mostly reported in the Press -- in UK, the Financial Times (FT)
Transactions are conducted say by Investment Houses (IH) or whoever anywhere in the world (eg Standard Life or many others in UK )
I want to find the the TV knowing N hence I need to look up UP
EXAMPLE On 24/12/2020 Investment House 'IH' sells N units of Fund 'Y'. What is the transaction value? The IH can only process the sale on a stock exchange, There is no quoted UP for Fund Y on 24/812/2020 so the actual transaction can only go ahead when trading in Fund Y resumes on 29/12/2020.
How can I lookup find the next available UP (i.e. on 29/12/2020) for a transaction dated 24/12/2020 from the following table?
Thanking you in advance Bornerways
This is a table the Values:

Col NoCDEFGH
Row No
18​
FUND PRICES (All day)
19​
20​
Funds
21​
DateDayWXYZ
22​
27-Nov-20Fri82.6077.5215.90191.79
23​
28-Nov-20Sat
24​
29-Nov-20Sun
25​
30-Nov-20Mon82.6777.4215.80190.99
26​
01-Dec-20Tue82.7677.8215.90191.41
27​
02-Dec-20Wed82.9977.7815.95191.93
28​
03-Dec-20Thur83.1477.8015.94191.60
29​
04-Dec-20Fri83.2578.0116.01192.09
30​
05-Dec-20Sat
31​
06-Dec-20Sun
32​
07-Dec-20Mon83.6478.4916.07193.24
33​
08-Dec-20Tue83.8178.4216.08193.46
34​
09-Dec-20Wed83.6978.4216.07193.02
35​
10-Dec-20Thur83.7978.6416.13193.96
36​
11-Dec-20Fri83.9278.8116.14194.24
37​
12-Dec-20Sat
38​
13-Dec-20Sun
39​
14-Dec-20Mon83.6978.6416.07193.35
40​
15-Dec-20Tue83.8778.5016.08193.16
41​
16-Dec-20Wed83.7078.5616.08193.05
42​
17-Dec-20Thur83.6878.6216.09193.11
43​
18-Dec-20Fri83.8478.8016.13193.50
44​
19-Dec-20Sat
45​
20-Dec-20Sun
46​
21-Dec-20Mon83.9278.7016.12193.69
47​
22-Dec-20Tue83.8478.6416.12193.94
48​
23-Dec-20Wed83.7078.6916.05192.56
49​
24-Dec-20Thur83.7278.54192.91
50​
25-Dec-20Fri
51​
26-Dec-20Sat
52​
27-Dec-20Sun
53​
28-Dec-20Mon
54​
29-Dec-20Tue83.9579.1716.19194.26
55​
30-Dec-20Wed84.2779.0416.14193.91
56​
31-Dec-20Thur84.1478.9016.11193.52
57​
01-Jan-21Fri
58​
02-Jan-21Sat
59​
03-Jan-21Sun
60​
04-Jan-21Mon84.3279.2216.17193.96
61​
05-Jan-21Tue84.3379.1616.16193.93
62​
06-Jan-21Wed84.4379.2716.32194.46
63​
07-Jan-21Thur84.6279.6016.36194.85
64​
08-Jan-21Fri85.0079.8616.37195.31
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,457
Office Version
  1. 365
Platform
  1. Windows
Welcome to the MrExcel board!

This task would be considerably easier with a later version of Excel. ;)
However, see if this does what you want. In your version the G16 formula may need to be confirmed in the cell with Ctrl+Shift+Enter, not just Enter.

21 03 13.xlsm
CDEFGH
15DateFundIdxUnitsTV
1624/12/2020Y31001,619.00
17
18FUND PRICES (All day)
19
20Funds
21DateDayWXYZ
2227-Nov-20Fri82.677.5215.9191.79
2328-Nov-20Sat
2429-Nov-20Sun
2530-Nov-20Mon82.6777.4215.8190.99
261-Dec-20Tue82.7677.8215.9191.41
272-Dec-20Wed82.9977.7815.95191.93
283-Dec-20Thur83.1477.815.94191.6
294-Dec-20Fri83.2578.0116.01192.09
305-Dec-20Sat
316-Dec-20Sun
327-Dec-20Mon83.6478.4916.07193.24
338-Dec-20Tue83.8178.4216.08193.46
349-Dec-20Wed83.6978.4216.07193.02
3510-Dec-20Thur83.7978.6416.13193.96
3611-Dec-20Fri83.9278.8116.14194.24
3712-Dec-20Sat
3813-Dec-20Sun
3914-Dec-20Mon83.6978.6416.07193.35
4015-Dec-20Tue83.8778.516.08193.16
4116-Dec-20Wed83.778.5616.08193.05
4217-Dec-20Thur83.6878.6216.09193.11
4318-Dec-20Fri83.8478.816.13193.5
4419-Dec-20Sat
4520-Dec-20Sun
4621-Dec-20Mon83.9278.716.12193.69
4722-Dec-20Tue83.8478.6416.12193.94
4823-Dec-20Wed83.778.6916.05192.56
4924-Dec-20Thur83.7278.54192.91
5025-Dec-20Fri
5126-Dec-20Sat
5227-Dec-20Sun
5328-Dec-20Mon
5429-Dec-20Tue83.9579.1716.19194.26
5530-Dec-20Wed84.2779.0416.14193.91
5631-Dec-20Thur84.1478.916.11193.52
571-Jan-21Fri
582-Jan-21Sat
593-Jan-21Sun
604-Jan-21Mon84.3279.2216.17193.96
615-Jan-21Tue84.3379.1616.16193.93
626-Jan-21Wed84.4379.2716.32194.46
637-Jan-21Thur84.6279.616.36194.85
648-Jan-21Fri8579.8616.37195.31
TV
Cell Formulas
RangeFormula
E16E16=MATCH(D16,E21:H21,0)
G16G16=INDEX(INDEX(E22:H64,0,E16),SMALL(IF(C22:C64>=C16,IF(INDEX(E22:H64,0,E16)<>"",ROW(C22:C64)-ROW(C22)+1)),1))*F16
 

Watch MrExcel Video

Forum statistics

Threads
1,129,285
Messages
5,635,321
Members
416,852
Latest member
kanaikls

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
Top