Forum Discussion
Partial Miss Match of Vlookup
That 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.
That 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.
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. ^-^
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.
SergeiBaklan Thank you for your help! It seems that I need to update my Excel. O(∩_∩)O
daitou , you are welcome, glad to help