vlookup - first subsequent/previous cell value if searched value not found

[GregMax]

Hello,

please I need help with one problem.

I have currency list for a period and specific dates I need to get the currency for.
E.g.
A B
24.07.2017 7,4098 kn
21.07.2017 7,4110 kn
20.07.2017 7,4103 kn
19.07.2017 7,4143 kn
13.07.2017 7,4138 kn
11.07.2017 7,4111 kn

If date is e.g. 19.07.2017 (in A8) the searched value, with vlookup formula VLOOKUP(A8; $A$1:$B$6;2;FALSE) (in B8) would give 7,4143 kn.

But when I have e.g. 23.7.2017 the vlookup function displays #n/a, because for that date there is no currency value. In these cases, how can I edit formula to search for first previous valid date and displays currency value from that date? In this case this would be 21.7.2017 and value I would need is 7,4110kn.

I assume it is simple solution, but....

thx!!

 

[etaf]

order the table from earliest to latest date
and use
=VLOOKUP(A8,$A$1:$B$6,2,TRUE)

 

[rpaulson]

quick answer is to sort you list from oldest date to newest date Ie: 11.07.2017 in A1 and 24.07.2017 in A6. then remove the false from your formula.

hth,

Ross

 

[etaf]

Excel Workbook
	A	B	C
1	11/07/2017	74,111	kn
2	13/07/2017	74,138	kn
3	19/07/2017	74,143	kn
4	20/07/2017	74,103	kn
5	21/07/2017	74,110	kn
6	24/07/2017	74,098	kn
7
8	23/07/2017	#N/A	74110
Sheet1

 

[GregMax]

Hey,

thank you very very much both of you! I did not expect such a quick answers

Greg

 

[etaf]

you are welcome