Excel:在一个表列中查找值,并在稀疏填充的另一列中找到最近的值 [英] Excel: Lookup value in one table column and find the nearest value in another column that is sparsely populated
问题描述
╔═════════════════╗
║日期║值║
╠═════════════════════$ $ $ $ $ $ $ $║║║║║║║║Jan Jan Jan Jan Jan Jan Jan Jan Jan Jan Jan Jan Jan Jan Jan Jan Jan 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║
╚═══════ ═══════════╝
p>这个方法对我有用:
假设你的数据在 A2:B18
范围和你的ta rget date 08-Jan-14 in C1
,那么可以使用以下数组公式:
= VLOOKUP(C1,IF(B2:B18,A2:B18),2,1)
$ b注意,它是一个数组公式,所以你需要按 CTRL + SHIFT + ENTER
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 ║
╚═══════════╩═══════╝
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
这篇关于Excel:在一个表列中查找值,并在稀疏填充的另一列中找到最近的值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!