Forum Discussion

ericj123's avatar
ericj123
Copper Contributor
Apr 13, 2021

Vlookup that choose info from a formula instead of selfwriting column

Hi.   At first i´m not that very good at english, have that in mind ^^   I use to work with the formula vlookup and in those cases i use to do like this: In columns A1, A2, A3 and so forth I hav...
Formulas and Functions
HansVogelaar's avatar
HansVogelaar
MVP
Apr 13, 2021

ericj123 

Please attach a sample workbook without sensitive data that demonstrates the problem.

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Apr 13, 2021

    ericj123 

    Thanks! This is an exasperating aspect of Excel. You'd expect the 0.1 in E6 to match up with the 0.1 in B93. You see 0.1 in both cells after all, and they are formatted as General.

    But if you enter the formula =E6=B93 in a cell, it'll return FALSE, so Excel sees them as different.

    If you select B93, you'll see 0.099999999999999 in the formula bar. In fact, all values from B60 are off.

    This appears to be the cause of the problem.

    To correct this:

    In B4, enter the formula =ROUND(B3-0.01,2)

    If you use comma as decimal separator, it'd be =ROUND(B3-0,01;2)

    Double-click the fill handle in the lower right corner of B4 to fill the formula down.

    The formula =E6=B93 now returns TRUE. So everything should be OK now.

    But infuriatingly, it isn't - the VLOOKUP formula still returns #N/A. Grrr...

    I don't know why, but we can get around this by changing the formula in E6 to =CEILING.MATH(E5,0.01) (or CEILING.MATH(E5;0,01) if comma is the decimal separator)

    Weird, isn't it?

Resources