(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]

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

 

[Toadstool]

Hi Bornerways,
Can you give an example of your data with a worked example?
XL2BB would be preferred.

 

[bornerways]

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 No	C	D	E	F	G	H
Row No
18​	FUND PRICES (All day)
19​
20​			Funds
21​	Date	Day	W	X	Y	Z
22​	27-Nov-20	Fri	82.60	77.52	15.90	191.79
23​	28-Nov-20	Sat
24​	29-Nov-20	Sun
25​	30-Nov-20	Mon	82.67	77.42	15.80	190.99
26​	01-Dec-20	Tue	82.76	77.82	15.90	191.41
27​	02-Dec-20	Wed	82.99	77.78	15.95	191.93
28​	03-Dec-20	Thur	83.14	77.80	15.94	191.60
29​	04-Dec-20	Fri	83.25	78.01	16.01	192.09
30​	05-Dec-20	Sat
31​	06-Dec-20	Sun
32​	07-Dec-20	Mon	83.64	78.49	16.07	193.24
33​	08-Dec-20	Tue	83.81	78.42	16.08	193.46
34​	09-Dec-20	Wed	83.69	78.42	16.07	193.02
35​	10-Dec-20	Thur	83.79	78.64	16.13	193.96
36​	11-Dec-20	Fri	83.92	78.81	16.14	194.24
37​	12-Dec-20	Sat
38​	13-Dec-20	Sun
39​	14-Dec-20	Mon	83.69	78.64	16.07	193.35
40​	15-Dec-20	Tue	83.87	78.50	16.08	193.16
41​	16-Dec-20	Wed	83.70	78.56	16.08	193.05
42​	17-Dec-20	Thur	83.68	78.62	16.09	193.11
43​	18-Dec-20	Fri	83.84	78.80	16.13	193.50
44​	19-Dec-20	Sat
45​	20-Dec-20	Sun
46​	21-Dec-20	Mon	83.92	78.70	16.12	193.69
47​	22-Dec-20	Tue	83.84	78.64	16.12	193.94
48​	23-Dec-20	Wed	83.70	78.69	16.05	192.56
49​	24-Dec-20	Thur	83.72	78.54		192.91
50​	25-Dec-20	Fri
51​	26-Dec-20	Sat
52​	27-Dec-20	Sun
53​	28-Dec-20	Mon
54​	29-Dec-20	Tue	83.95	79.17	16.19	194.26
55​	30-Dec-20	Wed	84.27	79.04	16.14	193.91
56​	31-Dec-20	Thur	84.14	78.90	16.11	193.52
57​	01-Jan-21	Fri
58​	02-Jan-21	Sat
59​	03-Jan-21	Sun
60​	04-Jan-21	Mon	84.32	79.22	16.17	193.96
61​	05-Jan-21	Tue	84.33	79.16	16.16	193.93
62​	06-Jan-21	Wed	84.43	79.27	16.32	194.46
63​	07-Jan-21	Thur	84.62	79.60	16.36	194.85
64​	08-Jan-21	Fri	85.00	79.86	16.37	195.31

 

[Peter_SSs]

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
	C	D	E	F	G	H
15	Date	Fund	Idx	Units	TV
16	24/12/2020	Y	3	100	1,619.00
17
18	FUND PRICES (All day)
19
20			Funds
21	Date	Day	W	X	Y	Z
22	27-Nov-20	Fri	82.6	77.52	15.9	191.79
23	28-Nov-20	Sat
24	29-Nov-20	Sun
25	30-Nov-20	Mon	82.67	77.42	15.8	190.99
26	1-Dec-20	Tue	82.76	77.82	15.9	191.41
27	2-Dec-20	Wed	82.99	77.78	15.95	191.93
28	3-Dec-20	Thur	83.14	77.8	15.94	191.6
29	4-Dec-20	Fri	83.25	78.01	16.01	192.09
30	5-Dec-20	Sat
31	6-Dec-20	Sun
32	7-Dec-20	Mon	83.64	78.49	16.07	193.24
33	8-Dec-20	Tue	83.81	78.42	16.08	193.46
34	9-Dec-20	Wed	83.69	78.42	16.07	193.02
35	10-Dec-20	Thur	83.79	78.64	16.13	193.96
36	11-Dec-20	Fri	83.92	78.81	16.14	194.24
37	12-Dec-20	Sat
38	13-Dec-20	Sun
39	14-Dec-20	Mon	83.69	78.64	16.07	193.35
40	15-Dec-20	Tue	83.87	78.5	16.08	193.16
41	16-Dec-20	Wed	83.7	78.56	16.08	193.05
42	17-Dec-20	Thur	83.68	78.62	16.09	193.11
43	18-Dec-20	Fri	83.84	78.8	16.13	193.5
44	19-Dec-20	Sat
45	20-Dec-20	Sun
46	21-Dec-20	Mon	83.92	78.7	16.12	193.69
47	22-Dec-20	Tue	83.84	78.64	16.12	193.94
48	23-Dec-20	Wed	83.7	78.69	16.05	192.56
49	24-Dec-20	Thur	83.72	78.54		192.91
50	25-Dec-20	Fri
51	26-Dec-20	Sat
52	27-Dec-20	Sun
53	28-Dec-20	Mon
54	29-Dec-20	Tue	83.95	79.17	16.19	194.26
55	30-Dec-20	Wed	84.27	79.04	16.14	193.91
56	31-Dec-20	Thur	84.14	78.9	16.11	193.52
57	1-Jan-21	Fri
58	2-Jan-21	Sat
59	3-Jan-21	Sun
60	4-Jan-21	Mon	84.32	79.22	16.17	193.96
61	5-Jan-21	Tue	84.33	79.16	16.16	193.93
62	6-Jan-21	Wed	84.43	79.27	16.32	194.46
63	7-Jan-21	Thur	84.62	79.6	16.36	194.85
64	8-Jan-21	Fri	85	79.86	16.37	195.31
TV

Cell Formulas
Range	Formula
E16	E16	=MATCH(D16,E21:H21,0)
G16	G16	=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