VLOOKUP working on the calculation rather than the number shown

[Palacemad]

HI, I am sure this is a very quick question/answer,

In one cell(D38) I have a SUMIFS formula, the answer it is returning is 0.415. This number is actually a calculation of months in decimals. In the next cell (E38) I have a vlookup to return this figure in months. Cell D38 is rounded to 2 decimal places and is showing 0.42. The vlookup is still producing a returning value of 4 months, even though it should be 5 months. I have even tried using ROUNDUP within the VLOOKUP and this is still not having an impact.

I assumed the reason is due to the VLOOUP working on the actual calculation figure of 0.415, rather than the figure that is showing. However, I am unsure why it is not changing when the ROUNDUP formula is being used.

Any suggestions will be appreciated!

Thank you

 

[AlanY]

is your vlookup() similar to this?

Book1
	A	B	C	D	E	F
1	0.415	0.42	2		0.41	1
2					0.42	2
3					0.43	3
4					0.44	4
5					0.45	5
Sheet2

Cell Formulas
Range	Formula
B1	=ROUND(A1,2)
C1	=VLOOKUP(B1,E1:F5,2,0)

 

[Palacemad]

To clarify too, the formula in E38 when using ROUNDUP is:

=IF(COUNTBLANK(C38)=1,"",ROUNDUP(VLOOKUP(C38,DecimalToAge,2),0)&" months")

I am fairly certain that is written correctly, happy to proved wrong though!

 

[AlanY]

looks like the bracket is in the wrong place, try

=IF(COUNTBLANK(C38)=1,"",ROUNDUP(VLOOKUP(C38,DecimalToAge,2,0)&" months"))

 

[Palacemad]

Apologies, I am confusing issues here with writing the wrong cell references down.

[TABLE="width: 250"]
<tbody>[TR]
[TD]C38[/TD]
[TD]D38[/TD]
[/TR]
[TR]
[TD]Calculation returning the figure 0.415 but showing 0.42 as rounded to 2dp by formatting the cell properties

[/TD]
[TD]=IF(COUNTBLANK(C38)=1,"",ROUNDUP(VLOOKUP(C38,DecimalToAge,2),2)&" months")[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Palacemad]

Alan, I get a response of I have entered too few arguments for this section, the word 'months' at the end of the formula is then highlighted

 

[AlanY]

my bad, try again

=IF(ISBLANK(C38),"",ROUNDUP(VLOOKUP(C38,DecimalToAge,2,0),2)&" months")

 

[Palacemad]

Thank you, but it is now returning #N/A

Sorry! I did copy and paste your formula rather than typing it to make sure it is correct.

 

[AlanY]

this works for me

Book1
	A	B	C	D	E
1	0.415	0.42 months		0.41	1
2				0.42 months	2
3				0.43	3
4				0.44	4
5				0.45	5
6
7					2 months
Sheet2

Cell Formulas
Range	Formula
B1	=ROUND(A1,2)&" months"
E7	=IF(ISBLANK(B1),"",ROUNDUP(VLOOKUP(B1,D1:E5,2,0),2)&" months")

 

[AlanY]

show us what's in C38 and the table DecimalToAge