Forum Discussion

daitou's avatar
daitou
Copper Contributor
Mar 22, 2020
Solved

Partial Miss Match of Vlookup

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 spa...
excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Mar 22, 2020

    daitou 

    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.

Charla74's avatar
Charla74
Iron Contributor
Mar 22, 2020
....I would also suggest that you wrap your formula in an IFERROR function, to avoid the #N/A....then you can calculate the numbers in the result to check that it matches the lookup table.

=IFERROR(VLOOKUP(E2,A$1:B$300,2,FALSE),"") [Enter in cell F2]
daitou's avatar
daitou
Copper Contributor
Mar 22, 2020
Sure, I will keep it in mind. 👌

Resources