CODEWITHSUNDEEP
excelexcel-formulavlookup
FinancialRadDeveloper
FinancialRadDeveloper

Reputation: 994

Excel: Lookup value in one table column and find the nearest value in another column that is sparsely populated

I have a table of data such as below in Excel. I would love to know how to find the nearest value to the correct data. So a worked example I can do a VLOOKUP or INDEX MATCH for 6th Jan. However if I lookup for 8th Jan I will get 0, but I want to get the nearest value, or if easier I would like to get the last known previous value from the next column which is 103. For the 19th Jan I would like 92 etc. I can code this up in VBA but I would really like an Excel formula to do this and I can't think how to do it! 

╔═══════════╦═══════╗
║   Date    ║ Value ║
╠═══════════╬═══════╣
║ 05-Jan-14 ║ 102   ║
║ 06-Jan-14 ║ 103   ║
║ 07-Jan-14 ║       ║
║ 08-Jan-14 ║       ║
║ 09-Jan-14 ║       ║
║ 10-Jan-14 ║       ║
║ 11-Jan-14 ║       ║
║ 12-Jan-14 ║ 99.9  ║
║ 13-Jan-14 ║       ║
║ 14-Jan-14 ║       ║
║ 15-Jan-14 ║       ║
║ 16-Jan-14 ║       ║
║ 17-Jan-14 ║       ║
║ 18-Jan-14 ║ 92    ║
║ 19-Jan-14 ║       ║
║ 20-Jan-14 ║       ║
║ 21-Jan-14 ║ 94    ║
╚═══════════╩═══════╝

Upvotes: 1

Views: 2298

Answers (1)

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35843

This approach works for me:

Suppose your data are in A2:B18 range and your target date 08-Jan-14 in C1, then you can use following array formula:

=VLOOKUP(C1,IF(B2:B18<>"",A2:B18),2,1)

Note, that it is an array formula, so you need to press CTRL+SHIFT+ENTER

Upvotes: 1

Related Questions