Vlookup that choose info from a formula instead of selfwriting column

Copper Contributor

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 have names for example... And in columns B1, B2, B3 and so forth I may have a number. In C1 I have my formula vlookup and in D1 I have my search column.

Usually i type in my reference by my own in D1... BUT in this case I have a formula there.

 

This formula is dependent on many reasons but is allways giving me the exact number or name so the match should work with the A and B columns...

 

But in my case I don´t get the right. It just says that the number can´t match.

 

My question is: Have I done something wrong or is it just that simple that this method just don´t work?

 

Thanks kindly for any help!

4 Replies

@ericj123 

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

I just found out that its on the 0,43 line that everything seems to go wrong. Why, I don´t know ..

@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?