Mar 22 2020 08:31 AM - edited Mar 22 2020 08:35 AM
What I want to do is to find every number of column A in column E and print the relating value in B.
I find that almost 10% of the number is missed. I have checked all the possible errors like space or format.
And it is strange that when I find the boxes which are wrong and then re-enter the quoted box, it works as correct. (The video records that phenomenon)
Therefore, I assume that there might be some systematic error with Vlookup function?
In the test file, you can find that some numbers, such as 11.37 and 11.63, are not matched correctly.
Thank you for your help.
Sincerely,
Xinyu
Mar 22 2020 10:02 AM
Highlight column E, goto the Data tab, click Text to Columns, check that the Delimited radio button is checked and click finish.
Mar 22 2020 10:05 AM
Mar 22 2020 11:40 AM
SolutionThat is rounding error issue, more about it here EXCEL: RANK, COUNTIF, AND FLOATING POINT ERRORS If check xml components of your file, quite many numbers looks like
I don't know how did you take them, better to pull numbers correctly. Otherwise use something like
=VLOOKUP(ROUND(E2,15),ROUND(A$1:B$300,15),2,FALSE)
and take into account rounding error could appear in other calculations as well.
Mar 22 2020 05:19 PM
Thank you very much! It does work ~\(≧▽≦)/~@Charla74
Mar 22 2020 05:44 PM - edited Mar 22 2020 05:48 PM
The origin data is calculated by another formula that might generate such floating numbers.
Moreover, I find that =VLOOKUP(ROUND(E2,15),ROUND(A$1:B$300,15),2,FALSE) will get #VALUE! result, however, if I change it into =VLOOKUP(ROUND(E2,15),A$1:B$300,2,FALSE) then it will get the correct results.
I have downloaded your test file. It shows the correct results at first but when I re-enter the same formula, it turns into #VALUE! error again. Does it mean that my Excel is lack of some plugin?
Anyway, I appreciate your comprehensive explanation. It gives me further insight. ^-^
Mar 23 2020 01:26 AM
I guess you are on Excel without dynamic array, thus formula shall be used as array one, i.e. entered with Ctrl+Shift+Enter. Anyway, if rounding only lookup value is enough you may use this variant of the formula.
Mar 27 2020 12:59 AM
@Sergei Baklan Thank you for your help! It seems that I need to update my Excel. O(∩_∩)O
Mar 22 2020 11:40 AM
SolutionThat is rounding error issue, more about it here EXCEL: RANK, COUNTIF, AND FLOATING POINT ERRORS If check xml components of your file, quite many numbers looks like
I don't know how did you take them, better to pull numbers correctly. Otherwise use something like
=VLOOKUP(ROUND(E2,15),ROUND(A$1:B$300,15),2,FALSE)
and take into account rounding error could appear in other calculations as well.