VLOOKUP在数组中找不到值 [英] VLOOKUP not finding value in array

问题描述

我使用VLOOKUP函数在数组中找到一个值，但是尽管数组中可用，但某些值仍给出了#N/A答案.

I used VLOOKUP function to find a value in an array but some of the values gave #N/A answer despite of available in array.

为了对数字进行四舍五入，我使用了CEILING函数，但是有趣的一点是某些值，它没有用.

To round up the numbers, I used CEILING function but interesting point is in some values, it did not work.

我检查了值的类型是否为数字.

I checked the type of value if it is number or not.

此外，我使用了ROUNDUP函数，但是没有用.

Also, I used ROUNDUP function but did not work.

此外，我尝试使用INDEX/MATCH组合，但再次无效.

Also, I tried INDEX/MATCH combination and again did not work.

在我在链接中给出的示例中，当我在15.00-15.20之间输入内容时，会出现错误，但尝试其他值则可以.

In the example that I gave in the link, when I type between 15.00 - 15.20, it gives error but trying other values, it works.

我该如何解决?

推荐答案

使用CEILING的返回值，这似乎是VLOOKUP和MATCH的错误.如果您使用:

This seems to be a bug with VLOOKUP and MATCH using the return values of CEILING. If you use:

=VLOOKUP(ROUND(CEILING(F4,0.1),1),A:B,2,FALSE)

然后按预期工作.

如果我们使用VBA进行研究，那么我们会看到发生了什么.真正应该责怪的是CEILING和ROUNDUP.参见示例:

If we look at this with VBA then we see what happens. To blame should be really CEILING and ROUNDUP. See example:

Sub testCeilingAndRoundup()

 Dim v As Double, test As Boolean, diff As Double

 v = [CEILING(15.1,0.1)] '15.1
 test = (v = 15.1) 'FALSE
 diff = 15.1 - v '-1.776...E-15

 v = [ROUNDUP(15.25,1)] '15.3
 test = (v = 15.3) 'FALSE
 diff = 15.3 - v '1.776...E-15

End Sub

这篇关于VLOOKUP在数组中找不到值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！