vlookup a rounded up value

[bucknut11]

I need to vlookup a rounded up number to the nearest tenth and then return a separate value that cordinates with the rounded number from table in separate sheet:

WS1 .......................................................................WS2

.........A............B ........................P ...........................................Q
1 ..... PH .. INT Value ........1 . Actual PH ................................INT Value
2..... (6.0) .. (1.250) ..........2 .. (6.04) ........................Should kick out 1.500 not 1.250
3..... (6.1) .. (1.500) ..........3 .. (6.24) ........................Should kick out 2.000 not 1.750
4..... (6.2) .. (1.750) ..........4 .. (6.34) ........................Should kick out 2.250 not 2.000
5..... (6.3) .. (2.000) ..........5 .. (6.40) ........................Should kick out 2.250
6..... (6.4) .. (2.250) ..........6 .. (6.21) ........................Should kick out 2.000 not 1.750


I have tried inserting a fake column that does the round up pre-vlookup and refrenced it as my search number but it still kicks out the lower INT value.

Thanks

 

[Andrew Poulsom]

Welcome to MrExcel.

Formula in E2 copied down:

Excel 2010
	A	B	C	D	E
1	PH	INT Value	1	Actual PH	INT Value
2	6.0	1250	2	6.04	1500
3	6.1	1500	3	6.24	2000
4	6.2	1750	4	6.34	2250
5	6.3	2000	5	6.40	2250
6	6.4	2250	6	6.21	2000
Sheet1

Cell Formulas
Range	Formula
E2	=LOOKUP(CEILING(D2,0.1),A$2:A$6,B$2:B$6)

 

[hiker95]

bucknut11,

Welcome to the MrExcel forum.

How about?

Excel 2007
	A	B	C	P	Q
1	PH	INT Value		Actual PH	INT Value
2	6.0	1.250		6.04	1.500
3	6.1	1.500		6.24	2.000
4	6.2	1.750		6.34	2.250
5	6.3	2.000		6.40	2.250
6	6.4	2.250		6.21	2.000
7
Sheet1

Cell Formulas
Range	Formula
Q2	=LOOKUP(CEILING(P2,0.1),A$2:A$6,B$2:B$6)

The formula in cell Q2, copied down:
=LOOKUP(CEILING(P2,0.1),A$2:A$6,B$2:B$6)

 

[hiker95]

bucknut11,

How about?

Excel 2007
	A	B
1	PH	INT Value
2	6.0	1.250
3	6.1	1.500
4	6.2	1.750
5	6.3	2.000
6	6.4	2.250
7
WS1

Excel 2007
	P	Q
1	Actual PH	INT Value
2	6.04	1.500
3	6.24	2.000
4	6.34	2.250
5	6.40	2.250
6	6.21	2.000
7
WS2

Cell Formulas
Range	Formula
Q2	=LOOKUP(CEILING(P2,0.1),'WS1'!$A$2:$A$6,'WS1'!$B$2:$B$6)

The formula in cell Q2, copied down:
=LOOKUP(CEILING(P2,0.1),'WS1'!$A$2:$A$6,'WS1'!$B$2:$B$6)

 

[bucknut11]

thanks...

will be posting a new topic today... on a table pulling from left column and top row to find result where they meet.

 

[hiker95]

bucknut11,

Thanks for the feedback.

You are very welcome. Glad we could help.

And, come back anytime.