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

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
Asked:
Tosagua
  • 3
  • 3
  • 3
  • +1
2 Solutions
 
Missus Miss_SellaneusCommented:
If value you are looking up is in b1, for example:

=VLOOKUP(ROUNDUP(b1,-1),{{range}}, col #)
0
 
TosaguaAuthor Commented:
Missus Miss_Sellaneus,

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

Tosagua
0
 
Missus Miss_SellaneusCommented:
There's no file.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
TosaguaAuthor Commented:
Sorry about that.

Tosagua
VLOOKUP---ROUNDUP.xlsx
0
 
Missus Miss_SellaneusCommented:
=VLOOKUP(ROUNDUP(C30,1),(E$30:F$40), 2)

I didn't actually have the fields formatted as % when I tried earlier.
0
 
Rob HensonFinance 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
 
TosaguaAuthor 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
 
ProfessorJimJamCommented:
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
 
Rob HensonFinance AnalystCommented:
No it doesn't, it gives 7 as expected.
0
 
ProfessorJimJamCommented:
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
 
Rob HensonFinance 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
 
ProfessorJimJamCommented:
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

  • 3
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now