LOOKUP Issue and #####

B

Brown

Board Regular
Joined
Sep 14, 2009
Messages
200
Office Version
  1. 365
I am having issues with a couple of things, and I hope you can help.
I am using a LOOKUP function someone on here gave me, and it worked fine until I tried to adjust it, here it is;
=IF(C8="","",LOOKUP(C8,{"A","AP","B","BP","C","CP","D","DP","F"},{4,5,3,4,2,3,1,2,0}))
what I am wanting to add is another letter and number sequence, such as
=IF(C8="","",LOOKUP(C8,{"A","AP","AH","B","BP","BH","C","CP","CH","D","DP","DH""F"},{4,5,4.5,3,4,3.5,2,3,2.5,1,2,1.5,0}))
Any ideas why that wont work?
Also in the following table I keep getting #### in the cells where I have no numbers and it is throwing off my calculations, because there will not always be something in the box to calculate.
<TABLE style="WIDTH: 147pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=195><COLGROUP><COL style="WIDTH: 42pt; mso-width-source: userset; mso-width-alt: 2048" width=56><COL style="WIDTH: 43pt; mso-width-source: userset; mso-width-alt: 2084" width=57><COL style="WIDTH: 31pt; mso-width-source: userset; mso-width-alt: 1499" span=2 width=41><TBODY><TR style="HEIGHT: 30pt" height=40><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: white; WIDTH: 42pt; HEIGHT: 30pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl70 height=40 width=56>Cred. Hr.</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 43pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl63 width=57>Letter</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 31pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl63 width=41>GPA</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 31pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl63 width=41>Total</TD></TR><TR style="HEIGHT: 18.75pt" height=25><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 18.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 height=25>0.5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71>B</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>1.5</TD></TR><TR style="HEIGHT: 18.75pt" height=25><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 18.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 height=25>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71>ap</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>5</TD></TR><TR style="HEIGHT: 18.75pt" height=25><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 18.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 height=25>0.5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71>bp</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>2</TD></TR><TR style="HEIGHT: 18.75pt" height=25><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 18.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=25>0.5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71>b</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>1.5</TD></TR><TR style="HEIGHT: 18.75pt" height=25><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 18.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=25>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71>c</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>2</TD></TR><TR style="HEIGHT: 18.75pt" height=25><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 18.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=25>0.5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71>AH</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>2</TD></TR><TR style="HEIGHT: 19.5pt" height=26><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 19.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl74 height=26> </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72> </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl73> </TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl73>###</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #c5d9f1; HEIGHT: 15.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl77 height=21>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl78> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl78> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #c5d9f1; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #d4d0c8" class=xl79 align=middle>#####</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: white; HEIGHT: 15.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl67 height=21> </TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #75923c; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl75>AVG</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #75923c; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl76>#####</TD></TR></TBODY></TABLE>
Thanks for the help guys.
Brown
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Sort by date Sort by votes
I did alittle testing and I think your formula isn't working because they are not in alphabetical Order.

I think you formula should become:

Code: 
=IF(C8="","",LOOKUP(C8,{"A","AH","AP","B","BH","BP","C","CH","CP","D","DH","DP","F"},{4,4.5,5,3,3.5,4,2,2.5,3,1,1.5,2,0}))

As alphabetically AH preceeds AP, BH preceeds BP etc etc
 
Upvote 0
AH should come before AP, as should BH,CH and DH come before BP, CP and DP. LOOKUP expects the array to be sorted ascending.
 
Upvote 0
Understood, Thanks

any suggestiosn on the other part of my issue?

Thanks Guys!!!

THAT DID IT!!
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
622
smozgur
smozgur
T
  • Question Question
Exporting Cell Range As PNG - Getting A White Border To The Bottom & Right - Why?
2
Replies
16
Views
180
jolivanes
jolivanes
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
E
  • Question Question
VBA code clean up
Replies
9
Views
580
mumps
mumps
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G

Forum statistics

Threads
1,224,570
Messages
6,179,608
Members
452,930
Latest member
racefanjtd

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top