Excel Lookup

How can I make this formula, lookup properly.

=IF(OR(BT21={"P","F","I","NC","WF","WP"},SUM(BT21)>0),VLOOKUP(BT21,TABLES!$A$2:$C$108,2,TRUE),"")

If BT21 is 92.997 it looks up the table with 92, but I need it to round up to 93.00 and look at 93, is there a way to do this in the Formula?
LVL 20
LazarusAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jppintoCommented:
Try changing the TRUE by FALSE on the VLOOKUP function.
0
ScriptAddictCommented:
You can use the formula ROUNDUP(number, Number of digits)
0
ScriptAddictCommented:
=IF(OR(BT21={"P","F","I","NC","WF","WP"},SUM(BT21)>0),VLOOKUP(ROUNDUP(BT21,0),TABLES!$A$2:$C$108,2,TRUE),"")
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

LazarusAuthor Commented:
jppinto, changing the VLOOKUP to false will not work.

ScriptAddict, Where would I put it?
0
LazarusAuthor Commented:
ScriptAddict, I have found the what to do, thank you:

=IF(OR(BT21={"P","F","I","NC","WF","WP"},SUM(BT21)>0),VLOOKUP(ROUNDUP(BT21,2),TABLES!$A$2:$C$108,2,TRUE),"")

0
barry houdiniCommented:
Do you always want to round up? Should 92.001 round up to 93 or down to (2?

barry
0
Patrick MatthewsCommented:
Seems odd that you would use a mix of text and numbers, but anyway:

=IF(OR(BT21={"P","F","I","NC","WF","WP"},SUM(BT21)>0),VLOOKUP(IFERROR(VALUE(TEXT(BT21,"0")),BT21),TABLES!$A$2:$C$108,2,TRUE),"")
0
LazarusAuthor Commented:
I spoke to soon as this will not work either, as it changes the Possible letter grades to #VALUE!, I will have to reopen the Question
0
Patrick MatthewsCommented:
To always round up...

=IF(OR(BT21={"P","F","I","NC","WF","WP"},SUM(BT21)>0),VLOOKUP(IFERROR(ROUNDUP(BT21,0),BT21),TABLES!$A$2:$C$108,2,TRUE),"")
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LazarusAuthor Commented:
Thats the ticket, Thank you, this fixes the issue completey from what I see so far.
0
ScriptAddictCommented:
Sorry I was little slow on the draw there :(
0
Patrick MatthewsCommented:
lazarus98,

If you want to reopen the question, click the Request Attention link and ask the Mods to reopen it for you.

Patrick
0
LazarusAuthor Commented:
Already did that matthewspatrick, put thanks.
0
barry houdiniCommented:
Like Patrick said, I'm not sure it's a great idea to mix text and numbers - perhaps it would be better to have two separate lookup tables (one for text and one for numbers)  and then use a formula like

=VLOOKUP(IFERROR(ROUNDUP(BT21,0),A1),IF(N(BT21),NumTable,LetTable),2)

regards, barry
0
barry houdiniCommented:
Actually, thinking about it, it's almost always a bad idea to have letters in a table with a TRUE lookup table as you can get some incorrect results if the wrong letters are input. Change my above suggestion to

=IF(N(BT21),VLOOKUP(ROUNDUP(BT21,0),NumTable,2), VLOOKUP(BT21,LetTable,2,0))

regards, barry

0
LazarusAuthor Commented:
barryhoudini,
I agree that the mixing or Numbers and Letters is not optimum, I will look into a possible change, but this spreadsheet is rahter large already
You also asked this "Do you always want to round up? Should 92.001 round up to 93 or down to (2?"
92.9727272727273 would be 92.97, but a 92.9977272727273 would be 93.00, does that make sense to you?
 
It's a mangled issue really, the Lookup table use 0-100 numbers for grading so a 92 = B+ or a 93=A-

0
ScriptAddictCommented:

=IF(OR(BT21={"P","F","I","NC","WF","WP"},SUM(BT21)>0),VLOOKUP(IFERROR(ROUNDUP(BT21,2),BT21),TABLES!$A$2:$C$108,2,TRUE),"")

I think this might round a little more to your liking.
0
LazarusAuthor Commented:
Yes, this works much better. I think this will work.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

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.