Solved

# Excel Lookup

Posted on 2011-10-14
Medium Priority
266 Views
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
Question by:Lazarus
• 7
• 4
• 3
• +2

LVL 33

Expert Comment

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

LVL 11

Expert Comment

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

LVL 11

Expert Comment

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

LVL 20

Author Comment

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

ScriptAddict, Where would I put it?
0

LVL 20

Author Comment

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

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

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

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

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

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

LVL 11

Expert Comment

ID: 36970334
Sorry I was little slow on the draw there :(
0

LVL 93

Expert Comment

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

ID: 36970393
Already did that matthewspatrick, put thanks.
0

LVL 50

Expert Comment

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

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

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

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

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

## Featured Post

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.
###### Suggested Courses
Course of the Month13 days, 23 hours left to enroll