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

Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

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

#### bornerways

##### New Member
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?
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

##### MrExcel MVP, Moderator
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

1,129,766
Messages
5,638,236
Members
417,016
Latest member
Tegguy

### 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.

### Which adblocker are you using?

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

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