?
Solved

Excel Lookup

Posted on 2011-10-14
19
Medium Priority
?
266 Views
Last Modified: 2012-05-12
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?
0
Comment
Question by:Lazarus
  • 7
  • 4
  • 3
  • +2
18 Comments
 
LVL 33

Expert Comment

by:jppinto
ID: 36970031
Try changing the TRUE by FALSE on the VLOOKUP function.
0
 
LVL 11

Expert Comment

by:ScriptAddict
ID: 36970040
You can use the formula ROUNDUP(number, Number of digits)
0
 
LVL 11

Expert Comment

by:ScriptAddict
ID: 36970078
=IF(OR(BT21={"P","F","I","NC","WF","WP"},SUM(BT21)>0),VLOOKUP(ROUNDUP(BT21,0),TABLES!$A$2:$C$108,2,TRUE),"")
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 20

Author Comment

by:Lazarus
ID: 36970080
jppinto, changing the VLOOKUP to false will not work.

ScriptAddict, Where would I put it?
0
 
LVL 20

Author Comment

by:Lazarus
ID: 36970096
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
 
LVL 50

Expert Comment

by:barry houdini
ID: 36970111
Do you always want to round up? Should 92.001 round up to 93 or down to (2?

barry
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 36970116
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
 
LVL 20

Author Comment

by:Lazarus
ID: 36970149
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
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 1400 total points
ID: 36970157
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
 
LVL 20

Author Comment

by:Lazarus
ID: 36970188
Thats the ticket, Thank you, this fixes the issue completey from what I see so far.
0
 
LVL 11

Expert Comment

by:ScriptAddict
ID: 36970334
Sorry I was little slow on the draw there :(
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 36970372
lazarus98,

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

Patrick
0
 
LVL 20

Author Comment

by:Lazarus
ID: 36970393
Already did that matthewspatrick, put thanks.
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 36970583
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
 
LVL 50

Expert Comment

by:barry houdini
ID: 36970702
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
 
LVL 20

Author Comment

by:Lazarus
ID: 36970810
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
 
LVL 11

Assisted Solution

by:ScriptAddict
ScriptAddict earned 600 total points
ID: 36970885

=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
 
LVL 20

Author Comment

by:Lazarus
ID: 36970993
Yes, this works much better. I think this will work.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question