• Status: Solved
• Priority: Medium
• Security: Public
• Views: 480

# VLOOKUP, ROUNDUP RETURN VALUE

This should be simple, but I can't find a solution.
I tried VLOOKUP, but it returns a lower value, and I need it to ROUNDUP.
The lookup value is 17%
The Lookup Table is:
0.0%    10
10.0%  9
20.0%  8
30.0%  7
40.0%  6
etc.
The return value that should be returned is 8, because 17% is greater than 10%
If the lookup value is 25%, the return value should be 7.

Any advice or insight would be greatly appreciated.

Tosgua
0
Tosagua
• 3
• 3
• 3
• +1
2 Solutions

Commented:
If value you are looking up is in b1, for example:

=VLOOKUP(ROUNDUP(b1,-1),{{range}}, col #)
0

Author Commented:
Missus Miss_Sellaneus,

I may have missed something, but to the return value is not correct.
See attached file.

Tosagua
0

Commented:
There's no file.
0

Author Commented:

Tosagua
VLOOKUP---ROUNDUP.xlsx
0

Commented:
=VLOOKUP(ROUNDUP(C30,1),(E\$30:F\$40), 2)

I didn't actually have the fields formatted as % when I tried earlier.
0

Finance AnalystCommented:
You can use the INDEX and MATCH functions rather than VLOOKUP:

=INDEX(B2:B6,MATCH(D2,A2:A6,1)+1)

B2:B6 is 10, 9, 8, 7, 6 etc
D2 is lookup value
A2:A6 is 0%, 10%, 20%, 30%, 40%

For example of 17%, this returns 8 as expected. The MATCH function finds the value in range A2:A6 which is closest but not greater than the lookup value and returns a number representing the relative row in the table of that value, the +1 then gives the next row, for 17% the MATCH would give row 2 for 10% as the next option of 20% is greater than 17%. The +1 then gives 3. INDEX then looks at the third value in range B2:B6 and returns 8.

NOTE: This does rely on the % values being in ascending order.

Thanks
Rob H
0

Author Commented:
Missus Miss_Sellaneus , Rob Henson,

This is always the hard part. Both answers are excellent and work equally well. I really enjoyed Rob's explanation of how Index-Match functioned.

I thank both of you for your assistance in getting me out of a tough spot.

Tosagua
0

Commented:
Missus Miss_Sellaneus  solutions works more accurately, becuase if you change the reject rate to 21%  the Vlookup returns 7, while the index and match formula by Rob still sits on 8.
0

Finance AnalystCommented:
No it doesn't, it gives 7 as expected.
0

Commented:
yes, because you added +1 at the end.  but lets see if the lookup value is 30% then vlookup return correctly 7, while the index and match returns 6 .  attached is the file.
VLOOKUP---ROUNDUP.xlsx
0

Finance AnalystCommented:
The +1 was there all along but yes for those values where there is an exact match it goes to next row.

Correction to allow for exact match:

=INDEX(B2:B6,IFERROR(MATCH(D2,A2:A6,0),MATCH(D2,A2:A6,1)+1))

Thanks
Rob H
0

Commented:
this one nails it

:-)

thx Rob
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.